Results 1 to 4 of 4

Thread: VBA to email selected range in body of email and add the same selected range as an attachment

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to email selected range in body of email and add the same selected range as an attachment

    Hello!
    I'm hoping I explain this correctly but what I'm trying to do is use vba to send an email where the selected range is added to the body of the email and at the same time add the same selected range as an attachment to the email.
    I'm currently using Ron de Bruin's "Mail Range/Selection in the body of mail" http://www.rondebruin.nl/win/s1/outlook/bmail2.htm vba which works perfectly but I now need to email it as an attachment as well. I again used Ron de Bruin's "Mail Range or Selection" http://www.rondebruin.nl/win/s1/outlook/amail4.htm vba but I somehow need to compile these two vba so I'm not sending two separate emails (one with the range selection in the email body and another with the range selection as an attachment). Is there a way I can combine these two vba? Thanks in advance!

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,230
    Post Thanks / Like
    Mentioned
    69 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA to email selected range in body of email and add the same selected range as an attachment

    Try this

    Code:
    Sub Mail_Selection_Range_Outlook_Body()
    'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    'Don't forget to copy the function RangetoHTML in the module.
    'Working in Excel 2000-2016
        Dim rng As Range
        Dim OutApp As Object
        Dim OutMail As Object
    
    
        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
        'Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
    
    
        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
        
        Dim newBook As Workbook, wFile As String
        wFile = ThisWorkbook.Path & "\FileSelection.xlsx"
        Set newBook = Workbooks.Add
        rng.Copy Range("A1")
        newBook.SaveAs wFile
        newBook.Close False
        
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
    
        On Error Resume Next
        With OutMail
            .To = "ron@debruin.nl"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .Attachments.Add wFile
            .HTMLBody = RangetoHTML(rng)
            .Send   'or use .Display
        End With
        On Error GoTo 0
    
    
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    End Sub
    
    
    
    
    Function RangetoHTML(rng As Range)
    ' Changed by Ron de Bruin 28-Oct-2006
    ' Working in Office 2000-2016
        Dim fso As Object
        Dim ts As Object
        Dim TempFile As String
        Dim TempWB As Workbook
    
    
        TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
    
    
        '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
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Mar 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to email selected range in body of email and add the same selected range as an attachment

    Thanks Dante! I'll try this tomorrow!

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,230
    Post Thanks / Like
    Mentioned
    69 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA to email selected range in body of email and add the same selected range as an attachment

    Ok, try and tell me.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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