1

Hi I am trying to create a Macro which can send mail with embedded image but i want to have customize image dimension hence i want the macro to pick image size i.e width and Height mentioned in cell and resize it before sending a mail.

I also want to add another 2 images with web link attached to it, so that when user click on each link it takes them to it's respective page. But can't get code for same. Please help.

My code is as below

Sub SendMail()

Dim olApp As Object
Dim olMail As Object



For I = 2 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

With ws
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(olMailItem)


End With


With olMail
.To = Cells(I, 2).Value
.cc = Cells(I, 3).Value
.bcc = Cells(I, 4).Value
.Subject = Cells(I, 5).Value
.Body = Cells(I, 6).Value


On Error Resume Next
.Attachments.Add Cells(I, 7).Value


.HTMLBody = .HTMLBody & "<img src = 'image1.jpg' " 
& "width= Cells(I, 8).Value height=Cells(I, 9).Value ><br>"

.Attachments.Add Cells(I, 10).Value


On Error GoTo 0

.Display
.Send

End With

Next

Set olMail = Nothing
Set olApp = Nothing

MsgBox "Mails Sent Successfully"


End Sub`
0m3r
  • 12,286
  • 15
  • 35
  • 71
Rehaan
  • 23
  • 5

1 Answers1

1

The quoting is the culprit. I would surround with double quotes:

.HTMLBody = .HTMLBody & "<img src = 'image1.jpg' width=""" & Cells(I, 8).Value & """ height=""" & Cells(I, 9).Value & """ ><br>"
Florent B.
  • 41,537
  • 7
  • 86
  • 101
  • Thanks Florent. That worked. Can you also please help me with second part of my question – Rehaan Mar 24 '16 at 08:36
  • You should consider creating another question since the title of this one is answered. – Florent B. Mar 24 '16 at 09:09
  • Thanks once again Florent for your reply. I have created a new question and link is pasted below.http://stackoverflow.com/questions/36197295/multiple-embedded-images-in-mail-with-different-url-excel-vba – Rehaan Mar 24 '16 at 09:53
  • @Rehaan Please accept this answer if it has solved your problem. Click on the tick mark beside this answer. – Mangesh Mar 24 '16 at 13:44