Excel to outlook

Hi Jacqueline,

Thank you for your question.

In my example I have a table in range A2:F7 in a worksheet named Sheet1.

If you create a workbook with a table in range A2:F7 you can copy and paste the code and see how it is working.

Sub sendMail()
Application.Calculation = xlManual
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Dim TempFilePath As String

'Create a new Microsoft Outlook session
Set appOutlook = CreateObject("outlook.application")
'create a new message
Set Message = appOutlook.CreateItem(olMailItem)


With Message
.Subject = "My mail auto Object" 'the email subject
'the formatting and body text
.HTMLBody = "<span LANG=EN>" _
& "<p class=style2><span LANG=EN><font FACE=Calibri SIZE=3>" _
& "Hello,<br ><br >The weekly dashboard is available " _
& "<br>Find below an overview :<BR>"

'first we create the image as a JPG file. Change the range here it is a2:f7
Call createJpg("Dashboard", "a2:f7", "DashboardFile")
'we attached the embedded image with a Position at 0 (makes the attachment hidden)
TempFilePath = Environ$("temp") & "\"
.Attachments.Add TempFilePath & "DashboardFile.jpg", olByValue, 0

'Then we add an html <img src=''> link to this image
'Note than you can customize width and height - not mandatory

.HTMLBody = .HTMLBody & "<br><B>WEEKLY REPPORT:</B><br>" _
& "<img src='cid:DashboardFile.jpg'" & "width='400' height='80'><br>" _
& "<br>Best Regards,<br>Jens Bonde</font></span>"

.To = "contact1@email.com; contact2@email.com"
.CC = "contact3@email.com"

.Display
'.Send
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Application.Calculation = xlCalculationAutomatic
End Sub
Sub createJpg(Namesheet As String, nameRange As String, nameFile As String)
ThisWorkbook.Activate
Worksheets("Sheet1").Activate 'enter your sheet name with the table
Set Plage = ThisWorkbook.Worksheets("Sheet1").Range(nameRange) 'enter your sheet and range
Plage.CopyPicture
With ThisWorkbook.Worksheets("Sheet1").ChartObjects. _
Add(Plage.Left, Plage.Top, Plage.Width, Plage.Height)
.Activate
.Chart.Paste
.Chart.Export Environ$("temp") & "\" & nameFile & ".jpg", "JPG"
End With
Worksheets("Sheet1").ChartObjects(Worksheets("Sheet1").ChartObjects.Count).Delete
Set Plage = Nothing
End Sub

If you have a look in the code you will see that you need to create a chart object to create the image.

I hope this will do the job for you.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Read more: https://www.stl-training.co.uk/post-34560-formula-shows-quarters.html #ixzz2wE8kbKnu