Excel - VBA - Image in the E-mail Body - GMAIL

Asked

Viewed 3,801 times

-2

Excel VBA - EMAIL using GMAIL

Good morning, everyone!

I need to send an image in the body of the email, at the moment I am using the code below, as I do to include an image at the end of the text?

I tried to include the code below but it didn’t work... Send the email normally but without the image.

.HTMLBody = Email_Body & "<html><body><img src=""G:\SETOR DE CADASTRO\WELLINGTON\SIGN.jpg""></body></html>"

 Private Sub btnEmail_Click()
 Dim myMail As CDO.Message
 Set myMail = New CDO.Message
 Dim Login_EmailAddress, Login_EmailPassword, SMTPServer As String
 Dim ServerPort, x, linha As Integer
 Dim To_Email, CC_Email, BCC_Email, Email_Subject, Email_Body, Attachment_Path As String
 Dim FileExtn As String

 FileExtn = ".PDF"

 SMTPServer = "smtp.gmail.com"
 ServerPort = 465
 Login_EmailAddress = Sheet1.Range("O2").Value
 Login_EmailPassword = Sheet1.Range("O3").Value
 With myMail.Configuration.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = ServerPort
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = Login_EmailAddress
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = Login_EmailPassword
.Update
End With

Range("A1").Select
linha = Range("A999").End(xlUp).Row

For x = 2 To linha
To_Email = Cells(x, 1).Value
CC_Email = Cells(x, 2).Value
'BCC_Email = Sheet1.Range("L4").Value
Attachment_Path = Cells(x, 7).Value
Email_Subject = Cells(x, 8).Value
Email_Body = Cells(x, 9).Value

If Sheet1.Range("G2").Value <> "" Then
Sheet1.Calculate 'to refresh sheet
Attachment_Path = VBA.UCase(Sheet1.Range("G2").Value)

If VBA.InStr(Attachment_Path, FileExtn) > 0 Then Attachment_Path = VBA.Replace(Attachment_Path, FileExtn, "")

End If

With myMail
.From = Login_EmailAddress
.Subject = Email_Subject
.To = To_Email
.CC = CC_Email
.BCC = BCC_Email
.HTMLBody = Email_Body & "<html><body><img src=""G:\SETOR DE CADASTRO\WELLINGTON\SIGN.jpg""></body></html>"
If Attachment_Path <> "" Then .AddAttachment Attachment_Path & FileExtn
End With

On Error Resume Next
myMail.Send

If Err.Number <> 0 Then
MsgBox Err.Description, vbCritical
Else
Cells(x, 1).Interior.Color = vbYellow
End If

Next x

Set myMail = Nothing

End Sub

Okay! I saw in another question here something similar and I got an answer, but... It generated another doubt. The solution is in place of the image path, insert a link from a web image. This way works, however what I want to send I can not publish so on the web, I would like it to be taken from my shared drive, tried and could not, even changing the privacy settings for any WEB user.

excel-vba emailing

2 answers

0


One possible way, however not so practical, is to pre-publish the image in an external repository like Google or AWS and point to the image in HTML. 

.HTMLBody = Email_Body &  <html> <body><img src="https://www.freelogoservices.com/blog/wp-content/uploads/logo-color_image.jpg"></body></html>

Usually when I needed to do this, I would upload the image in Google Photos, locate the authentication-independent direct link and use it to attach my signature in a massive upload. I could not find another way to attach the image in the body of the email since it connected directly to the server via SMTP.

  • That is how I was able to do it, thank you for the reply!

-1

You have to include the image and hide it. The position 0 will add it and hide it, the 1 is the Outlook constant olByValue.

.Attachments.Add FILENAME, 1, 0

Once you add the image you must use the src as src="cid:FILENAME.jpg".

Try adding this line: 

.Attachments.Add "G:\SETOR DE CADASTRO\WELLINGTON\SIGN.jpg", 1, 0

And in html: 

.HTMLBody = Email_Body & "<html><body><img src='cid:SIGN.jpg'></body></html>"

  • Who voted against could at least say what’s wrong

  • Good morning! iamdIm, the code I shared above is to send email through GMAIL, without Outlook. Outlook functions do not work with it. I’m using CDO.message. the .Attachments.Add function doesn’t even work on it. I have already found several materials on the internet about doing this using outlook and it is as you reported above. But with GMAIL is different, By conscience disengagement I even tested this your code, but the error and neither wheel.

Browser other questions tagged excel-vba emailing

You are not signed in. Login or sign up in order to post.