Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Add image with hyperlink into email signature

0

Hello everyone,

I'm trying to create a code to add an image with hyper link (which was retrieved from a cell in excel sheet) into email signature. 

But my code keeps adding the image before the email body. I couldn't any way to adjust  this.

Attach is my code for reference. Really appreciate some help to resole this issue. Thank you very much.

Sub EmailSignature()
Dim SignatureImage As Shape Set SignatureImage = ThisWorkbook.Sheets("sheet1").Shapes("Picture 1") 'Copy the image to the clipboard SignatureImage.CopyPicture 'Create a new email message Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) 'Paste the image into the email message body and set the hyperlink With OutMail     .Display     .HTMLBody = "<html><body>" & _                 "<img src='cid:SignatureImage'>" & _                 "<p>Dear Sir/Madam,</p>" & _                 "<p>Please find below the report for this month:</p>" & _                 "<p>Best regards,</p>" & _                 "</body></html>"     .GetInspector.WordEditor.Range(0, 0).PasteAndFormat wdChartPicture     '.GetInspector.WordEditor.InlineShapes(1).Hyperlink.Address = "http://www.google.com" End With
End sub






excelmacros-vba
MS Office Version: 365
posted by: joevn93 (rep: 4) on Wed Jun 28, 2023 at 5:44 am
Answer
Discuss

Discussion

Hi joevn93

Could you use a solution which adds a shape or picture from a file location (rather than that associated with a cell in Excel) or allow it to be saved out from the cell then added?
John_Ru (rep: 6142) Jun 28, '23 at 6:29 pm
Did my Answer work?
John_Ru (rep: 6142) Jul 6, '23 at 6:04 am
Add to Discussion

Answers

0

Hi Joevn93

You can move the <img src= element to the end of the HTMLBody= text creation and wrap the hyperlink around the inline shape at the same time. That portion of the HTML becomes:

"<a href="http://www.example.com"><img src='cid:SignatureImage'> </a>" & _

and the full code below includes other changes in bold that you'll need to consider/add (uncomment):

Sub EmailSignature()

    Dim SignatureImage As Shape
    Set SignatureImage = ThisWorkbook.Sheets("sheet1").Shapes("Picture 1")

    'Copy the image to the clipboard
    SignatureImage.CopyPicture

    'Create a new email message
    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    'Paste the image into the email message body and set the hyperlink
    With OutMail
        '.To = "<<email address>>"
        '.Subject ="Monthly report"
        .HTMLBody = "<html><body>" & _
                    "<p>Dear Sir/Madam,</p>" & _
                    "<p>Please find below the report for this month:</p>" & _
                    "<p>Best regards,</p>" & _
                    "<a href="http://www.example.com"><img src='cid:SignatureImage'> </a>" & _
                    "</body></html>"
        .Display
        ' uncomment following to send email
        ' .Send

    End With

    ' close Outlook
    'Set OutMail = Nothing
    'Set OutApp = Nothing

End Sub

That code can be run from Module1 of the revised file attached.

Hope this fixes your probem- if so, please remember to mark this Answer as Selected.

posted by: John_Ru (rep: 6142) on Fri Jun 30, 2023 at 11:34 am
Discuss

Discussion

Did you see my Answer? (Many people seem to miss alert emails sent over the weekend) 
John_Ru (rep: 6142) Jul 4, '23 at 1:29 am
Okay, looks like I wasted my time (again!)
John_Ru (rep: 6142) Jul 8, '23 at 3:50 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login