Excel sheet to outlook - VBA

[RJVMS]

Hello,

I help your help and experience to help me solve a thing. I have an excel file with some values and formatting done and I have a macro that sends that info to the mail body, but I also have to put 2 or 3 images in that same mail body.
Is this even possible??


Thanks in advance for the replies

[Kenneth Hobs]

Welcome to the forum! The answer is yes.

The two methods in Outlook are either htmlBody or Word Editor. Ron de Bruin explains the methods with examples at: https://www.rondebruin.nl/win/s1/outlook/mail.htm

In the htmlBody method, add html code tags like "img src". In the Word Editor method, add the images to a range, and then copy and paste using Word methods.

[gmayor]

If you want to insert images in the body of the text as opposed to attaching them, then you can use the Word document inspector to process the message body with VBA as if it was a Word document without having to worry about HTML tags e.g. the following is based on a code I have posted previously, with the addition of two images.

It is basically a matter of setting the ranges to where you want the texts and images. In this example there is text, followed by the Excel range, followed by text, then an image, more text, a second image, and again more text. Note the comments in the code!


Sub SendWorkBook()
'Graham Mayor - https://www.gmayor.com - Last updated - 02 Dec 2019
'This macro requires the code from
'http://www.rondebruin.nl/win/s1/outlook/openclose.htm
'to open Outlook


Dim oOutlookApp As Object
Dim oItem As Object
Dim olInsp As Object
Dim wdDoc As Object
Dim oRng As Object, oImg As Object
Dim xlRng As Range
Const strImg1 As String = "C:\Path\Before.png" 'The first image path
Const strImg2 As String = "C:\Path\After.png" 'The second image path


Set xlRng = Range("$I$22:$M$36") 'The range to be copied


xlRng.Copy 'Copy it


Set oOutlookApp = OutlookApp() 'Use the function from http://www.rondebruin.nl/win/s1/outlook/openclose.htm
'to open Outlook, or it will not work correctly


'Create a new mailitem
Set oItem = oOutlookApp.CreateItem(0)
With oItem
'Address the message
.To = "someone@somewhere.com"
'Give it a title
.Subject = "This is the subject"
.BodyFormat = 2 'html
Set olInsp = .GetInspector
Set wdDoc = olInsp.WordEditor 'access the message body for editing
Set oRng = wdDoc.Range
oRng.collapse 1 'set a range to the start of the message

oRng.Text = "This is the message text before the Excel range:" & vbCr & vbCr
'Collapse the range to its end
oRng.collapse 0

oRng.Paste

oRng.End = wdDoc.Tables(1).Range.End + 1
oRng.collapse 0
oRng.Text = "This is the text after the Excel range." & vbCr & vbCr
'The range will be followed by the signature associated with the mail account


'add the first image at the end of the message before the signature

oRng.collapse 0
oRng.InlineShapes.AddPicture (strImg1)


Set oImg = wdDoc.Bookmarks("_MailAutoSig").Range
oImg.Start = oImg.Start - 1
oImg.collapse 1
oImg.Text = vbCr & vbCr & "This is the text after the first image" & vbCr & vbCr
oImg.collapse 0
'add second image
oImg.InlineShapes.AddPicture (strImg2)


Set oImg = wdDoc.Bookmarks("_MailAutoSig").Range
oImg.Start = oImg.Start - 1
oImg.collapse 1
oImg.Text = vbCr & vbCr & "This is the text after the second image" & vbCr & vbCr


'display the message - this line is required even if you then add the command to send the message
.Display
'.Send
End With


'Clean up
Set oItem = Nothing
Set oOutlookApp = Nothing
Set olInsp = Nothing
Set wdDoc = Nothing
Set xlRng = Nothing
Set oRng = Nothing
Set oImg = Nothing
lbl_Exit:
Exit Sub
End Sub

[itccexec]

good day gmayor,

please i require your help with the outlook mail auto referencing number, would like to know if to edit anything in the code as i have no knowledge on how to use VBA.

Best regards