VBA-HTML Email Send

[Mahantesh_Torgal]

Hi All,

I have been given a task, where I need to send emails in VBA with HTML style.

Please help me with the HTML part on how to use it in VBA scripting?

Thank you.
Mahantesh

 

[KOKOSEK]

Sub EmailWithRangeAsPicture()
    Dim olApp As Object
    Dim NewMail As Object
    Dim ChartName As String
    Dim imgPath As String
    
    On Error GoTo error
   
    Set olApp = CreateObject("Outlook.Application")
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Set NewMail = olApp.CreateItem(0)
    With NewMail
        .Subject = "test of HTML email"
        .To = "someone@smoewhere.com"
        .CC = ""
        .BCC = ""
        .HTMLBody = "Hi,<br>That's you information what you were looking for."
        .HTMLBody = .HTMLBody & "<br>regards<br><br>Excel EmailSender"
        .Display
        '.Send
    End With
error:
    Set olApp = Nothing
    Set NewMail = Nothing
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

'.Send' if you want to send email 'in background' or '.Display' if you want to get prepared email and send it manually.

 

[Mahantesh_Torgal]

Sub EmailWithRangeAsPicture()
    Dim olApp As Object
    Dim NewMail As Object
    Dim ChartName As String
    Dim imgPath As String
   
    On Error GoTo error
  
    Set olApp = CreateObject("Outlook.Application")
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    Set NewMail = olApp.CreateItem(0)
    With NewMail
        .Subject = "test of HTML email"
        .To = "someone@smoewhere.com"
        .CC = ""
        .BCC = ""
        .HTMLBody = "Hi,<br>That's you information what you were looking for."
        .HTMLBody = .HTMLBody & "<br>regards<br><br>Excel EmailSender"
        .Display
        '.Send
    End With
error:
    Set olApp = Nothing
    Set NewMail = Nothing
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

'.Send' if you want to send email 'in background' or '.Display' if you want to get prepared email and send it manually.

Hi ,

Thank you for quick response.

I am using CDO technique for VBA emailing.

Could you please send me the code that uses CDO technique?

Thanks a lot!!

 

[KOKOSEK]

I understand that you have got already most of code. You can paste your code, so we can check how it is looks like.

To create email in HTML, it should works:

Set Message = CreateObject("CDO.Message")
    With Message
        .From = FromAddress
        .To = ToAddress
        .Subject = Subject
        .HTMLBody = "Whatever<br>is<br>here<br>can be<br>formatted as <br>HTML"
    End With

 

[Mahantesh_Torgal]

I understand that you have got already most of code. You can paste your code, so we can check how it is looks like.

To create email in HTML, it should works:

Set Message = CreateObject("CDO.Message")
    With Message
        .From = FromAddress
        .To = ToAddress
        .Subject = Subject
        .HTMLBody = "Whatever<br>is<br>here<br>can be<br>formatted as <br>HTML"
    End With

Thank you for the response.

I am actually developing Birthday automation in VBA which will trigger "Happy Birthday Emails" with colorful Outlook email background.

Can we achieve this in VBA? if yes, can you send me a sample VBA script?

Regards,
Mahantesh

 

[KOKOSEK]

Check this thread, I've put some hints there how to do 'automat' to sending email.
Automatically send emails to specific addreses within the worksheet based on a value within a cell
Check post#2, of course you have to adapt it into your needs.

 

[Mahantesh_Torgal]

Hi KOKOSEK,

Thank you for the code, it worked out well. however, I have got one doubt...

I am sending an image using the <img> tag of HTML, however, the pictures are not getting downloaded automatically, I have to go to Trust center settings to change the settings.

If there is any VBA code to enable that directly, can you please help out?

Regards,
Mahantesh

 

[KOKOSEK]

if you put picture inside as IMG tag, you have to also attach it into email. Below example as insert chart from sheet into email.

   myPicture = "Chart1.png"
    myPath = Environ$("temp") & "\"
    fileName = myPath & myPicture
    
    myChart.Export fileName

    With Message
        .TO = "xyz@anc.com"
        .CC = "abc@xyz.com"
        .Subject = "Test"
        .Attachments.Add fileName, 0    ' HERE IS PICTURE ATTACHED WITH FULL PATH INTO IT
        .HTMLBody = "Blablabla" & "<img src=cid:" & myPicture & " height=240 width=180>" & "<p>regards</p>" & "<p>KOKOSEK</p>"
        .Display
    End With