1

I am trying to add Excel data to Outlook email.

This is an illustration of the output in an Outlook email editor. The img I'm trying to add should be add at the end, after the Excel content. This is a illustration of how the output in a outlook email editor. The img I'm trying to add should be add at the end, after the excel content

I tried various ways to add an image which is a footnote.

I tried adding the <img> tag to attach it as HTML attachment but it gets attached without any spacing.

Tried using these two lines initially 

.Attachments.Add "C:\Users\Sumit Jain\Pictures\11\city.jpg", olByValue, 0

.HTMLBody = .HTMLBody & "<img src='cid:city.jpg'><br>"

Then I tried making a default signature in Outlook.

The code

.HTMLBody = "<HTML><body><body></HTML>" & .HTMLBody

appends Outlook's default signature on the top and then the Excel content after.

Reference to page I used the logic from Link

Below is the code

Private Sub CommandButton9_Click()
On Error GoTo ERRORMSG
Dim OutApp As Object
Dim OutMail As Object
Dim olInsp As Object
Dim wdDoc As Object
Dim oRng As Object

Set otlApp = CreateObject("Outlook.Application")
Set olMail = otlApp.CreateItem(olMailItem)
Set Doc = olMail.GetInspector.WordEditor
Set mainWB = ActiveWorkbook

mainWB.Sheets("Mail").Range("m8").Value = ComboBox4.Value
mainWB.Sheets("Mail").Range("n8").Value = TextBox40.Value
mainWB.Sheets("Mail").Range("q8").Value = ComboBox5.Value
mainWB.Sheets("Mail").Range("r8").Value = ComboBox6.Value
mainWB.Sheets("Mail").Range("s8").Value = ComboBox7.Value
mainWB.Sheets("Mail").Range("t8").Value = TextBox44.Value

On Error Resume Next
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
    .To = mainWB.Sheets("Email").Range("A3").Value
    .cc = mainWB.Sheets("Mail").Range("L12").Value
    .Subject = mainWB.Sheets("Mail").Range("O15").Value
    Set olInsp = .GetInspector
    Set wdDoc = olInsp.WordEditor
    Set oRng = wdDoc.Range

    'force html format
    .HTMLBody = "<HTML><body><body></HTML>" & .HTMLBody
    .Display

    '--- start with 6 CrLf's, so we can place each table
    '    above all but the last used...
    oRng.InsertAfter vbCrLf & vbCrLf

    '--- now reselect the entire document, collapse our cursor to the end
    ' and back up one character (so that the table inserts before the SIXTH CrLf)
    Set oRng = wdDoc.Range
    oRng.collapse 0
    oRng.Move 1, -1
    Range("K3:T10").Select
    Selection.Copy
    oRng.Paste

    '--- finally move the cursor all the way to the end and paste the
    '    second table BELOW the SIXTH CrLf
    Set oRng = wdDoc.Range
    oRng.collapse 0
    Range("K38:T46").Select
    Selection.Copy
    oRng.Paste
End With
Exit Sub
End Sub
Community
  • 1
  • 1
Coding Enthusiast
  • 51
  • 1
  • 12
  • Should be `.Attachments.Add "C:\Users\Sumit Jain\Pictures\11\city.jpg", olByValue, 0` `.HTMLBody = " "` – 0m3r May 04 '17 at 09:22
  • Tried the above mentioned. This appends the picture in the start and then the excel content. Whereas I'm trying to get it the other way around – Coding Enthusiast May 04 '17 at 12:55
  • Can you an example image how you want it – 0m3r May 04 '17 at 18:03
  • I have edited the initial question with an illustration of the output in a outlook email editor. The img I'm trying to add should be added at the end, after the excel content. This is what I'm trying to achieve – Coding Enthusiast May 05 '17 at 10:52
  • Then it should be `.HTMLBody = .HTMLBody & vbCrLf & then the html here` also move your `.Display` above `.HTMLBody` – 0m3r May 09 '17 at 23:17
  • Here are many ideas https://stackoverflow.com/questions/8994116/how-to-add-default-signature-in-outlook and https://stackoverflow.com/questions/25571411/how-to-add-a-signature-in-excel-vba-email – niton Jul 10 '17 at 15:11

1 Answers1

0

Try the following in your code....

You need to add Mysig.htm to the name of your signature

SigString = Environ("appdata") & "\Microsoft\Signatures\" & UOutLookSign & ".htm"

If Dir(SigString) <> "" Then

   Signature = GetBoiler(SigString)

Else

   Signature = ""

End If
Rajesh C
  • 114
  • 4
  • The source provides complete information. http://www.rondebruin.nl/win/s1/outlook/signature.htm – niton Jul 10 '17 at 15:09