(Solved) – VBA Delete inserted signature in email body

(solved)-–-vba-delete-inserted-signature-in-email-body

I use this code to display email drafts with pasted sheet content in the body. The body includes a signature at the bottom which is not a desired outcome. How to remove it or not include in the first place?

    Sub create_drafts()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim Template As String
    Dim olInsp As Object
    Dim xlSheet As Worksheet
    Dim wdDoc As Object
    Dim oRng As Object

    Template = ThisWorkbook.Sheets("Prep").Range("I1")
    Select Case Template
    Case "IOPT"
        Set xlSheet = ThisWorkbook.Sheets("template1")
        xlSheet.UsedRange.Copy
    Case "IOPT (DSBP users)"
        Set xlSheet = ThisWorkbook.Sheets("template2")
        xlSheet.UsedRange.Copy
    Case "DSBP"
        Set xlSheet = ThisWorkbook.Sheets("template3")
        xlSheet.UsedRange.Copy
    End Select


    Set OutApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then Set OutApp = CreateObject("Outlook.Application")
    On Error GoTo 0

    Set OutMail = OutApp.CreateItem(0)
    
    With OutMail
        .SentOnBehalfOfName = "sharedmailbox@domain.com"
        .BodyFormat = 3
        .To = ""
        .Subject = ThisWorkbook.Sheets("Prep").Range("G1")
        Set olInsp = .GetInspector
        Set wdDoc = olInsp.WordEditor
        Set oRng = wdDoc.Range
        oRng.collapse 1
        oRng.Paste
        .Display
    End With
    On Error GoTo 0
    
    Set OutMail = Nothing
    Set OutApp = Nothing
    Set olInsp = Nothing
    Set wdDoc = Nothing
    Set oRng = Nothing

    End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *