Results 1 to 6 of 6

Thread: Need help with modification of VBA Macro to change the name of attachment in outlook

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2023
    Posts
    3

    Need help with modification of VBA Macro to change the name of attachment in outlook

    Hello Everyone,
    I am very new to VBA and tried at the moment stuck, I want that my file that is attaches to outlook to have a specific name "Stock Report", But I am not able to do it and it have 20% instead of space.

    Sharing the code below:
    Code:
    Function RangetoHTML(rng As range)
    ' Working in Office 2000-2016
        Dim fso As Object
        Dim ts As Object
        Dim TempFileName As String
        Dim TempFile As String
        Dim TempWB As Workbook
        Dim FileFullPath As String
    
        TempFile = Environ$("temp ") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".html"
        TempFileName = ActiveSheet.Name & "-" & Format(DateAdd("d", 2, Now), "dd-mmm-yy")
       
        'Copy the range and create a new workbook to past the data in
        rng.Copy
        Set TempWB = Workbooks.Add(1)
        With TempWB.Sheets(1)
            .Cells(1).PasteSpecial Paste:=8
            .Cells(1).PasteSpecial xlPasteValues, , False, False
            .Cells(1).PasteSpecial xlPasteFormats, , False, False
            .Cells(1).Select
            Application.CutCopyMode = False
            On Error Resume Next
            .DrawingObjects.Visible = True
            .DrawingObjects.Delete
            On Error GoTo 0
        End With
    
        'Publish the sheet to a htm file
        With TempWB.PublishObjects.Add( _
             SourceType:=xlSourceRange, _
             Filename:=TempFile, _
             Sheet:=TempWB.Sheets(1).Name, _
             Source:=TempWB.Sheets(1).UsedRange.Address, _
             HtmlType:=xlHtmlStatic)
            .Publish (True)
        End With
    
        'Read all data from the htm file into RangetoHTML
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
        RangetoHTML = ts.readall
        ts.Close
        RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                              "align=left x:publishsource=")
    
        'Close TempWB
        TempWB.Close savechanges:=False
    
        'Delete the htm file we used in this function
        Kill TempFile
    
        Set ts = Nothing
        Set fso = Nothing
        Set TempWB = Nothing
    End Function
    Please help, I am trying to modify this for 2 weeks now.

    Thank you in advance.

  2. #2
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,083

    Re: Need help with modification of VBA Macro to change the name of attachment in outl

    I don't see any code there that sends an email attachment.

    That being said, the only assistance I can give is to point you to this external thread:

    https://stackoverflow.com/questions/...names/77152861

  3. #3

    Thread Starter
    New Member
    Join Date
    Dec 2023
    Posts
    3

    Re: Need help with modification of VBA Macro to change the name of attachment in outl

    Quote Originally Posted by OptionBase1 View Post
    I don't see any code there that sends an email attachment.

    That being said, the only assistance I can give is to point you to this external thread:

    https://stackoverflow.com/questions/...names/77152861
    Hello,

    Thank you for the reply. I did check that already and it did not help me or might be I am doing something wrong.

    I shared only part of the code, the first half, is below:
    Code:
    Private Sub ClickToSend_Click()
    
       Dim rng As range
        Dim lRow As Integer
        Dim lCol As Integer
        Dim OutApp As Object
        Dim OutMail As Object
        Dim EmailDate As String
        Dim TempFilePath As String
        Dim FileExt As String
        Dim TempFileName As String
        Dim FileFullPath As String
        Dim FileFormat As Variant
    
        Set rng = Nothing
        On Error Resume Next
        'Only the visible cells in the selection
        'Set rng = Selection.SpecialCells(xlCellTypeVisible)
        'You can also use a fixed range if you want
        lRow = range("A1048576").End(xlUp).Row
        lCol = range("XFD1").End(xlToLeft).Column
        lRow = range("A1048576").End(xlUp).Row
    'use the lRow to help find the last column in the range
      lCol = range("XFD" & lRow).End(xlToLeft).Column
      Set rng = range(Cells(1, 1), Cells(lRow, lCol))
    'msgbox to show us the range
      MsgBox "Range is " & rng.Address
        On Error GoTo 0
        
        ' Create the date format (format : JJ/MM/AAAA)
    EmailDate = Format(DateAdd("d", 0, Now), "dd/mm")
    
        If rng Is Nothing Then
            MsgBox "The selection is not a range or the sheet is protected" & _
                   vbNewLine & "please correct and try again.", vbOKOnly
            Exit Sub
        End If
    
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
        On Error Resume Next
        With OutMail
            .To = ""
            .CC = ""
            .BCC = ""
            .Subject = "Stock Report " & EmailDate
            .HTMLBody = "<BODY style=font-size:11pt;font-family:Calibri>Hello Everyone," & "<br><br>" & "Please find latest updates:" & "<br><br>" & RangetoHTML(rng) & "<br><br>" & "Thank you,</BODY>" 'Remove the dots in the "br" portion of the string
            .Attachments.Add ActiveWorkbook.FullName
            .Display   'or use .Send
        End With
        On Error GoTo 0
    
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    End Sub

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,501

    Re: Need help with modification of VBA Macro to change the name of attachment in outl

    Quote Originally Posted by AlexVBA View Post
    Hello Everyone,
    I am very new to VBA and tried at the moment stuck, I want that my file that is attaches to outlook to have a specific name "Stock Report", But I am not able to do it and it have 20% instead of space.

    Sharing the code below:
    Code:
    Function RangetoHTML(rng As range)
    ' Working in Office 2000-2016
        Dim fso As Object
        Dim ts As Object
        Dim TempFileName As String
        Dim TempFile As String
        Dim TempWB As Workbook
        Dim FileFullPath As String
    
        TempFile = Environ$("temp ") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".html"
        TempFileName = ActiveSheet.Name & "-" & Format(DateAdd("d", 2, Now), "dd-mmm-yy")
       
        'Copy the range and create a new workbook to past the data in
        rng.Copy
        Set TempWB = Workbooks.Add(1)
        With TempWB.Sheets(1)
            .Cells(1).PasteSpecial Paste:=8
            .Cells(1).PasteSpecial xlPasteValues, , False, False
            .Cells(1).PasteSpecial xlPasteFormats, , False, False
            .Cells(1).Select
            Application.CutCopyMode = False
            On Error Resume Next
            .DrawingObjects.Visible = True
            .DrawingObjects.Delete
            On Error GoTo 0
        End With
    
        'Publish the sheet to a htm file
        With TempWB.PublishObjects.Add( _
             SourceType:=xlSourceRange, _
             Filename:=TempFile, _
             Sheet:=TempWB.Sheets(1).Name, _
             Source:=TempWB.Sheets(1).UsedRange.Address, _
             HtmlType:=xlHtmlStatic)
            .Publish (True)
        End With
    
        'Read all data from the htm file into RangetoHTML
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
        RangetoHTML = ts.readall
        ts.Close
        RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                              "align=left x:publishsource=")
    
        'Close TempWB
        TempWB.Close savechanges:=False
    
        'Delete the htm file we used in this function
        Kill TempFile
    
        Set ts = Nothing
        Set fso = Nothing
        Set TempWB = Nothing
    End Function
    Please help, I am trying to modify this for 2 weeks now.

    Thank you in advance.
    That's because the file is on the server and needs to be url encoded in order for the link to work. Solution is to not use spacces in the file name. Stock_Report or Stock-Report or StockReport should work.

    That said, there's nothing in that code that says Stock Report or does an attachment... It looks like it reads an Excel file and converts part of it to html and then does something with that. Also it looks like it creates a file... and then deletes it???


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,083

    Re: Need help with modification of VBA Macro to change the name of attachment in outl

    Try below as suggested in the link I posted earlier.

    Code:
        On Error Resume Next
        With OutMail
            .To = ""
            .CC = ""
            .BCC = ""
            .Subject = "Stock Report " & EmailDate
            .HTMLBody = "<BODY style=font-size:11pt;font-family:Calibri>Hello Everyone," & "<br><br>" & "Please find latest updates:" & "<br><br>" & RangetoHTML(rng) & "<br><br>" & "Thank you,</BODY>" 'Remove the dots in the "br" portion of the string
            With .Attachments.Add(ActiveWorkbook.FullName)
                .DisplayName = Replace(.DisplayName, "%20", " ")
            End With
            .Display   'or use .Send
        End With

  6. #6

    Thread Starter
    New Member
    Join Date
    Dec 2023
    Posts
    3

    Re: Need help with modification of VBA Macro to change the name of attachment in outl

    Quote Originally Posted by OptionBase1 View Post
    Try below as suggested in the link I posted earlier.

    Code:
        On Error Resume Next
        With OutMail
            .To = ""
            .CC = ""
            .BCC = ""
            .Subject = "Stock Report " & EmailDate
            .HTMLBody = "<BODY style=font-size:11pt;font-family:Calibri>Hello Everyone," & "<br><br>" & "Please find latest updates:" & "<br><br>" & RangetoHTML(rng) & "<br><br>" & "Thank you,</BODY>" 'Remove the dots in the "br" portion of the string
            With .Attachments.Add(ActiveWorkbook.FullName)
                .DisplayName = Replace(.DisplayName, "%20", " ")
            End With
            .Display   'or use .Send
        End With
    It worked, thank you very much for the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width