Hello Guys,
Please note I am trying to send an automatic email with HTML Images, so the idea is user can complete in some specific cell the address where is the image.
The VBA code works perfectly when the cells has the address , height and width. However when the email does not require any image or just one, its get an error in the email as string "Error!Filename not specified"
I would like if you can help me to fix a lit of bit the code so when the cell is empty, the code can avoid the HTML1 or HTML2 in .Body in EmailReminder (Vba Code) and remove "<br>" if the image is not required.
Thanks in advance
Andres A
Please note I am trying to send an automatic email with HTML Images, so the idea is user can complete in some specific cell the address where is the image.
The VBA code works perfectly when the cells has the address , height and width. However when the email does not require any image or just one, its get an error in the email as string "Error!Filename not specified"
I would like if you can help me to fix a lit of bit the code so when the cell is empty, the code can avoid the HTML1 or HTML2 in .Body in EmailReminder (Vba Code) and remove "<br>" if the image is not required.
VBA Code:
Sub TrackReminders()
Dim celda As Range
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.AskToUpdateLinks = False
On Error Resume Next
Sheets("Tracker - Reminders").Select
Range("D2").Select
For Each celda In Range("D2:D101")
If ActiveCell.Value = Empty Then
Exit Sub
Else
If ActiveCell.Value = Date Then
ActiveCell.Select
Call EmailReminder
End If
End If
ActiveCell.Offset(1, 0).Select
Next celda
End With
End Sub
Sub EmailReminder()
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.AskToUpdateLinks = False
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim strbody2 As String
On Error Resume Next
Set OutApp = CreateObject("Outlook.application")
Set OutMail = OutApp.CreateItem(0)
strbody = "<BODY style=font-size:12pt;font-family:Calibri> Hi Team" & "<br><br>" & "Hope you are doing great :)" & "<br><br>" & ActiveCell.Offset(0, 1).Value
strbody2 = "<BODY style=font-size:12pt;font-family:Calibri>" & ActiveCell.Offset(0, 2).Value
Signature = "<img src='cid:SignatureAndres.jpg'" & "width='300' height='150'>" & "</font></span>"
HTML1 = "<img src='" & ActiveCell.Offset(0, 4).Value & "'" & "width='" & ActiveCell.Offset(0, 7).Value & "' height='" & ActiveCell.Offset(0, 8).Value & "'><br>"
HTML2 = "<img src='" & ActiveCell.Offset(0, 6).Value & "'" & "width='" & ActiveCell.Offset(0, 9).Value & "' height='" & ActiveCell.Offset(0, 10).Value & "'><br>"
'_______________________________________________________________________________________________________________________________________________________________________________
With OutMail
On Error Resume Next
.To = ActiveCell.Offset(0, -2).Value
.CC = ActiveCell.Offset(0, -1).Value
'.BCC = ""
.Subject = ActiveCell.Offset(0, -3).Value
.Attachments.Add ActiveCell.Offset(0, 3).Value, olByValue, 0
.Attachments.Add ActiveCell.Offset(0, 5).Value, olByValue, 0
.Attachments.Add "C:\Users\amoreshe\OneDrive - Merck Sharp & Dohme, Corp\Desktop\Reminders Images\SignatureAndres.jpg", olByValue, 0
.HTMLBody = strbody & "<br><B></B><br>" & HTML1 & "<br>" & strbody2 & "<br>" & HTML2 & _
"<br>Best Regards</font></span>" & "<br>" & Signature
'.Send
.Display
'__________________________________________________________________________________________________________________________________________________________________________________
End With
Set OutMail = Nothing
End With
End SubThanks in advance
Andres A
