How to add excel range as a picture to outlook message body

Question

I'd like to build\edit the mail signiture in Excel:

 1st cell : |Regards, | 2nd cell (Name) : |Asaf Gilad | 3rd Cell (Title): |PMO | 4th cell (Mail) : |[email protected] |

So that when I click send, the body of the message will look like:

 Dear sir ................................ ....... Message Content ........ ................................ ................................ Regards, Asaf Gilad PMO [email protected]

The signiture contains pictures as well.

I managed to save the range as picture and send that picture as attachment, but the picture turned out to be empty in the body, dispite the fact that it was sent correctly as attachment.

Here is the code I use:

 Public Sub ExportEmail(recipentName As String) On Error GoTo err: Dim olApp As Outlook.Application Dim olNs As Outlook.Namespace Dim olMail As Outlook.MailItem Dim strEmailTo As String, strEmailCC As String, strEmailBCC As String Dim FNAME As String Dim oRange As Range Dim oChart As Chart Dim oImg As Picture strEmailTo = "" strEmailCC = "" strEmailBCC = "" strEmailTo = "[email protected]" strEmailCC = "[email protected] If strEmailTo "" Then Set olApp = New Outlook.Application Set olNs = olApp.GetNamespace("MAPI") olNs.Logon Set olMail = olApp.CreateItem(olMailItem) olMail.To = strEmailTo olMail.CC = strEmailCC olMail.BCC = strEmailBCC olMail.Subject = " My Subject" Set oRange = Sheets(1).Range("A1:Z100") Set oChart = Charts.Add oRange.CopyPicture xlScreen, xlPicture oChart.Paste FNAME = Environ$("temp") & "\testPic.gif" oChart.Export Filename:=FNAME, FilterName:="GIF" olMail.Attachments.Add FNAME olMail.HTMLBody = "" & _ "" olMail.Attachments.Add FNAME olMail.Send End If Application.StatusBar = False Application.ScreenUpdating = True Application.DisplayAlerts = True Kill FNAME Set olApp = Nothing Set olNs = Nothing Set oRange = Nothing Set oChart = Nothing Set oImg = Nothing Exit Sub err: MsgBox err.Description End Sub

why do you want to create signature adding programme when Outlook already has that wizard to add a signature easily? — bonCodigo
– bonCodigo, Commented Dec 17, 2012 at 13:01
I'm not sure yet. The customer defined this need. Maybe he wants emails sent by my tool to be signed differently than other mails. It's a good idea to ask him for the reason, nevertheless I find this excel-outlook task interesting. Beside the current task it may help me automate some mail composing (can't think of good example: maybe report summary) — Asaf Gilad
– Asaf Gilad, Commented Dec 17, 2012 at 14:13

Scott Holtzman · Accepted Answer · 2012-12-17 17:07:41Z

This is a good question, Asaf. When I have built automated e-mail solutions, I've found it difficult to get the signature line in. It's possible, but not easy. Maybe it's updated in 2010, but I haven't checked yet.

What I do is place the entire body into a text file on a drive, complete with any html tags that I want for formatting. This gives me great flexibility in both making nicely formatted e-mails where I can assign variables as well.

I then access those files through the Microsoft Scripting Runtime library.

See below code snippets:

Option Explicit Const strEmailBoiler As String = "\\server\path\folder\subfolder\email_text\" Sub PrepMessage() Dim strBody As String, strMon As String strMon = range("Mon").Value strFY = range("FY").Value strBody = FileToString(strEmailBoiler, "reports_email_body.txt") strBody = Replace(strBody, "[MONTH]", strMon) strBody = Replace(strBody, "[YEAR]", Right(strFY, 2)) strBody = Replace(strBody, "[FILE PATH]", strFileName) SendMail "[email protected]", "Subject Goes Here " & strMon & " YTD", strBody End Sub Function FileToString(ByVal strPath As String, ByVal strFile As String) As String 'requires reference to Microsoft Scripting Runtime Object Library (or late binding) Dim ts As TextStream Set fso = New FileSystemObject Set ts = fso.OpenTextFile(strPath & strFile, ForReading, False, TristateUseDefault) FileToString = ts.ReadAll ts.Close Set ts = Nothing Set fso = Nothing End Function Sub SendMail(strTo As String, strSubject As String, strHTMLBody As String, Optional strAttach As String, Optional strCC As String) 'requires reference to Microsoft Outlook X.X Object Library (or late binding) Dim olApp As Outlook.Application Dim olMI As Outlook.MailItem Set olApp = CreateObject("Outlook.Application") Set olMI = olApp.CreateItem(olMailItem) With olMI .To = strTo .Subject = strSubject .HTMLBody = strHTMLBody If strAttach <> vbNullString Then .Attachments.Add strAttach .Display 'using this because of security access to Outlook '.Send End With End Sub

Then my reports_email_body.txt file will look like this:

<p>Hello Person,</p> <p>The Reports file for [MONTH] FY[YEAR] has been saved in the following location:</p> <p><a href="[FILE PATH]">[FILE PATH]</a></p> <p>Best,</p> <br> Scott Holtzman <br>My Address <br>my title <br>whatever else...

Mike Marshall · Accepted Answer · 2014-11-13 20:58:46Z

In Excel 2010 (and possibly 2007) you can add .HTMLBody to the end of your body string. For instance, use something like this:

 .HTMLBody = "<br>" & strbody & .HTMLBody ' <br> is an HTML tag to turn the text into HTML ' strbody is your text from cell C9 on the mail tab ' .HTMLBody inserts your email signature

This will at least solve your signature line problem.

I am looking for a solution for the same problem: Inserting a range as a picture.

Collectives™ on Stack Overflow

How to add excel range as a picture to outlook message body

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related