How to send dynamic image in the body of the email with VBA

Asked

Viewed 43 times

0

I created a macro to send different images in the body of the email to different people. The problem is that I am not able to leave the dynamic image in the body of the email. It is attaching right, each image to its respective email. The problem is when I ask to leave visible the image does not open, staying blank or error. Follow the code I’m using:

Function Saudacao() As String
    Dim hora As Date
    
    hora = Format(Now, "HH:MM:SS")
    
    Select Case hora
        Case "00:00:00" To "11:59:59"
            Saudacao = "Bom dia!"
        Case "12:00:00" To "17:59:59"
            Saudacao = "Boa Tarde!"
        Case Else
             Saudacao = "Boa Noite!"
    End Select
End Function

Sub Enviar_Email()
On Error GoTo TRATAR_ERRO

Range("A5").Activate
Assunto = Range("R1").Value
Mensagem = Range("R3").Value

Do While ActiveCell.Value <> Empty
    
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    With OutMail
        
        .SentOnBehalfOfName = "[email protected]" 
        .Display
        .To = ActiveCell.Value
        .CC = ActiveCell.Offset(0, 2).Value
        .BCC = ""
        .Subject = Assunto & ActiveCell.Offset(0, 1).Value
        .Attachments.Add ActiveCell.Offset(0, 3).Value, , 1

        .HTMLBody = "<BR>" & ActiveCell.Offset(0, 1).Value & ", " & Saudacao & "<BR><BR>" & Mensagem & "<BR><BR>" & "<img src='ActiveCell.Offset(0, 3).Value'>" & "<BR>" & "Att," & "<BR>" & .HTMLBody
                       
        .Send

    End With

ActiveCell.Offset(1, 0).Select
ActiveCell.Activate


    Set OutMail = Nothing
    Set OutApp = Nothing
    
Loop
On Error GoTo 0

MsgBox "E-mails enviados com sucesso!", bvinformation, "Ok"
Exit Sub


TRATAR_ERRO:
Dim sErro As String
    sErro = Err.Number & vbNewLine & Err.Description
    MsgBox sErro, vbCritical + vbOKOnly, "Enviar_email"

End Sub
html vba excel-vba

  • I tried to do this in powershell and also could not, I was only able to put image in the body of the email when I refer to it as link in HTML format, I hope someone posts the answer.

1 answer

0

Hi, Talita!

That’s a common intuition. I add the image as Attachment and then put it in the IMG SRC body taking it from the same place on the computer and making the HTML render.

The problem is that the email doesn’t work exactly like that. When you have an image already in Attachment to go embedded (embeded) you need to use the CID:filename.jpg/png tag (no directory reference).

Soon:

... & "<img src='CID:" & Trim(Mid(ActiveCell.Offset(0, 3).Value, instrrev(ActiveCell.Offset(0, 3).Value, "\")+1, 2000)) &  "'>" & "<BR>" & "Att

This part of MID + Instrrev is to exclude the absolute path (c: users...), assuming I’m like this.

If not, or want to treat just put CID:image.png which is solved too

Browser other questions tagged html vba excel-vba

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