CODEWITHSUNDEEP
excelvbaoutlook
And180y
And180y

Reputation: 39

Formatting email body from Excel using VBA

I have found the code below from here https://stackoverflow.com/a/49207287/4539709

Option Explicit
Public Sub Example()
' add ref - tool -> references - > Microsoft Outlook XX.X Object Library
    Dim olApp As Outlook.Application
    Set olApp = New Outlook.Application

    Dim Email As Outlook.MailItem
    Set Email = olApp.CreateItem(0)

' add ref - tool -> references - > Microsoft Word XX.X Object Library
    Dim wdDoc As Word.Document '<=========
    Set wdDoc = Email.GetInspector.WordEditor

    Dim Sht As Excel.Worksheet
    Set Sht = ThisWorkbook.Worksheets("Sheet1")

    Dim rng As Range
    Set rng = Sht.Range("A4:H16").SpecialCells(xlCellTypeVisible)
        rng.Copy

    With Email
        .To = Sht.Range("C1")
        .Subject = Sht.Range("B1")
        .Display

         wdDoc.Range.PasteAndFormat Type:=wdFormatOriginalFormatting
    End With

End Sub

I have come across an issue with the code in that after you send an email the rows remain selected as per attached. Is there anyway to clear this

Upvotes: 1

Views: 1180

Answers (1)

0m3r
0m3r

Reputation: 12497

Add the following line at the end Application.CutCopyMode = False

    With Email
        .To = Sht.Range("C1")
        .Subject = Sht.Range("B1")
        .Display

         wdDoc.Range.PasteAndFormat Type:=wdFormatOriginalFormatting
    End With

    Application.CutCopyMode = False    '<---

End Sub

Upvotes: 1

Related Questions