I'm trying to create an Excel VBA, but my Outlook signature never shows. I want to add an image to my email body and had to use "HTMLbody", but then nothing worked. I tried to reset my computer, add a code to replicate a default signature of another email for this VBA.
This is my code:
Option Explicit
Sub enviar_email()
Dim intervalo As Range
Dim grafico As ChartObject
Dim Email As Object
Set intervalo = Sheet7.Range("A19:V54")
intervalo.CopyPicture
Set grafico = Sheet7.ChartObjects.Add(intervalo.Left, intervalo.Top, intervalo.Width, intervalo.Height)
With grafico
.Activate
.Chart.Paste
.Chart.Export Environ$("temp") & "/grafico.jpg"
.Delete
End With
Set Email = CreateObject("Outlook.application").createitem(0)
With Email
.to = "[email protected]"
.cc = "[email protected]"
.Subject = "MRP SEMANAL W" & Cells(28, 25)
.attachments.Add Environ$("temp") & "/grafico.jpg", 1, 0
.HTMLbody = "Bom dia time!" & "<br>" & "<br>" & _
"Segue abaixo o Mrp Semanal referente a W" & Cells(28, 25) & "<br>" & "<br>" & _
"<img src='cid:grafico.jpg'>" & "<br>" & "<br>" & _
"Obrigado." & "<br>" & "<br>"
.display
End With
End Sub
Can someone help me understand why it's not working?
CodePudding user response:
Outlook adds a signature only when you call Display
on a message with unmodified body. Your code modifies the message body before calling Display
. Moreover, it will be your responsibility to merge the HTML signature added by Outlook with your own HTML; keep in mind that two HTML strings cannot be concatenated, they must be merged.
See https://stackoverflow.com/a/71728029/332059 for more details.
CodePudding user response:
There are several ways to get the signature added to the mail item.
The first and easiest way to get it added by Outlook. To get this working you need to call the Display
method before modifying the message body.
With Email
.display
.to = "[email protected]"
.cc = "[email protected]"
.Subject = "MRP SEMANAL W" & Cells(28, 25)
.attachments.Add Environ$("temp") & "/grafico.jpg", 1, 0
.HTMLbody = Replace(.HTMLbody, "<body>", "<body> Bom dia time!" & "<br>" & "<br>" & _
"Segue abaixo o Mrp Semanal referente a W" & Cells(28, 25) & "<br>" & "<br>" & _
"<img src='cid:grafico.jpg'>" & "<br>" & "<br>" & _
"Obrigado." & "<br>" & "<br>")
End With
Note, in that case to preserve the existing signature (read the message body HTML markup) you need to insert your modifications before the signature. To do that you need to find the opening <body>
tag and add your content right after that HTML tag in the message body.
The second way is to read available signature and add it to your HTML markup to build the message body with a signature part. If you try to navigate to the following folder:
C:\Users\%username%\AppData\Roaming\Microsoft\Signatures
Each of the signatures you’ve created will have several associated files and a folder. The content may include:
- signature.txt – this is the signature file for plain text emails as it contains no formatting.
- signature.rtf – this is a ‘rich text format’ file and contains formatting.
- signature.htm – this is the HTML version of your email signature and is formatted using HTML tags.
- a folder containing other files such as colorschememapping.xml, filelist.xml, image001.png or themedata.thmx – these are the automatically generated files associated with the other HTML and rich text files.
So, you may grab the content and add it to your resulting HTML markup.