CODEWITHSUNDEEP
vbaexcelemailoutlook
Mr.Riply
Mr.Riply

Reputation: 845

email and signature formatting using Excel VBA

I have written the macros I need and Im stuck on a "simple matter". Email formatting as well as signature formatting. The signature is in plain text, but in outlook its formatted using different text color and font.

I have the following code which gives me the email in the following format:

email TITUS classification

Body of the email

Signature

Attachment

The code

Sub mail()

    Dim wkb As Excel.Workbook
    Dim wks As Excel.Worksheet

    Set wkb = Excel.Workbooks("macro.xlsm")
    Set wks = wkb.Worksheets("settings")

    Dim OApp As Object, OMail As Object, signature As String
    Dim OStrTITUS As String
    Set OApp = CreateObject("Outlook.Application")
    Set OMail = OApp.CreateItem(0)
        With OMail
        .display
        signature = OMail.body
        .To = "[email protected]"
        .Subject = "Type your email subject here"
        .Attachments.Add ActiveWorkbook.FullName
        .body = "My email body" & vbNewLine & signature
        .display
        SendKeys "{DOWN}{DOWN}{ENTER}", True 'set classification
        SendKeys "{ENTER}", True 'send to group
        .Send
        End With
Set OMail = Nothing
Set OApp = Nothing
End Sub

But I would like for the attachment in outlook to be in the body of the email, so the layout would be something like this

Body of the email

Attachment

Signature

Upvotes: 0

Views: 1057

Answers (1)

QHarr
QHarr

Reputation: 84465

You could try format as follows modified from code by @Niton 

Sub mail()

    Dim wkb As Excel.Workbook
    Dim wks As Excel.Worksheet

    Set wkb = Excel.Workbooks("macro.xlsm")
    Set wks = wkb.Worksheets("settings")

    Dim OApp As Object, OMail As Object, signature As String
    Dim OStrTITUS As String
    Set OApp = CreateObject("Outlook.Application")
    Set OMail = OApp.CreateItem(0)

    With OMail

        .Display
         signature = OMail.body
        .To = "[email protected]"
        .Subject = "Type your email subject here"
        .Display

        .body = "My email body" & vbNewLine & signature

        If .BodyFormat <> olFormatRichText Then .BodyFormat = olFormatRichText

        .Attachments.Add wkb.FullName, 999

        SendKeys "{DOWN}{DOWN}{ENTER}", True 'set classification
        SendKeys "{ENTER}", True 'send to group
        .Send
    End With

Set OMail = Nothing
Set OApp = Nothing

End Sub

Upvotes: 1

Related Questions