Emails from Excel (via VBA)

[Rile1]

Hi

Need your assistance on this

I have the following table and I am trying to
- automatically send an email via Excel based on the Status (Column 5) to the Travel Asst. (Column 22, as 1st notification point) and Travel Manager (Column 23, as escalation)
- in the escalation, copy the Travel Asst. and the copy the Travel Manager
- if there is a possibility to select a range of columns, and keep it in the body of the email (just a wish list)

Table
[TABLE="width: 855"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]22[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Status[/TD]
[TD]Travel Asst. Name[/TD]
[TD]Booking Asst. Name[/TD]
[TD]Status[/TD]
[TD]Travel Asst. Email[/TD]
[TD]Travel Manager Email[/TD]
[/TR]
[TR]
[TD]27-Aug-18[/TD]
[TD]Pending Booking[/TD]
[TD]ABC[/TD]
[TD]123[/TD]
[TD]Escalation to Travel Manager[/TD]
[TD]abc@gmail.com[/TD]
[TD]123@gmail.com[/TD]
[/TR]
[TR]
[TD]27-Aug-18[/TD]
[TD]Pending Presentation [/TD]
[TD]DEF[/TD]
[TD]456[/TD]
[TD]1st Notification to Travel Asst.[/TD]
[TD]yxz@gmail.com[/TD]
[TD]345@gmail.com[/TD]
[/TR]
[TR]
[TD]27-Aug-18[/TD]
[TD]Pending Presentation[/TD]
[TD]ZXY[/TD]
[TD]789[/TD]
[TD]2nd Notification to Travel Asst.[/TD]
[TD]abc@gmail.com[/TD]
[TD]123@gmail.com[/TD]
[/TR]
[TR]
[TD]27-Aug-18[/TD]
[TD]Pending Payment[/TD]
[TD]KLM[/TD]
[TD]101[/TD]
[TD]Escalation to Booking Manager[/TD]
[TD]yxz@gmail.com[/TD]
[TD]345@gmail.com[/TD]
[/TR]
</tbody>[/TABLE]

The code that i am trying to use for the same is the following
(Note: i am not sure if it is correct, as i tried to understand it from the web and modify it)

The error that displays when i try to run it is
Run- Time error"-2147467259 (80004005)':
We need to know who to send this to. Make sure you enter at least one name.

Note: there are 49 columns in the workbook

Start Code

Sub SendEscalationtoTravelManagerMail()
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim iCounter As Integer
Dim MailDest As String


Set OutLookApp = CreateObject("OutLook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)


With OutLookMailItem
For iCounter = 1 To WorksheetFunction.CountA(Columns(49))
If MailDest = "1st Notification to Travel Asst." And Cells(iCounter, 5) = "1st Notification to Travel Asst." Then
MailDest = Cells(iCounter, 22).Value
ElseIf MailDest = "2nd Notification to Travel Asst." And Cells(iCounter, 5) = "2nd Notification to Travel Asst." Then
MailDest = Cells(iCounter, 22).Value
ElseIf MailDest = "Escalation to Travel Manager" And Cells(iCounter, 5) = "Escalation to Travel Manager" Then
MailDest = MailDest & ";" & Cells(iCounter, 23).Value
End If
Next iCounter


.BCC = MailDest
.Subject = "Pending Booking"
.Body = "Reminder: Booking is due. Please complete."
.Send
End With


End Sub

End Code

Please if somebody could assist me on this

Thanking you

 

[davesexcel]

Your code does indicate the ".to:" address

 

[Rile1]

Hi Dave

Thanks for advising but I was not to clear on the same
I tried to amend the BCC to To but still it is unable to email

.To = MailDest
.Subject = "Pending Booking"
.Body = "Reminder: Booking is due. Please complete."
.Send

Please advise on the above to the "To" is to be added or is it on a different section

Thanking you

 

[davesexcel]

 If MailDest = "1st Notification to Travel Asst." And Cells(iCounter, 5) = "1st Notification to Travel Asst." Then

"MailDest" is not set so the criteria will never be met.

Possibly seeing if the cell.value = "1st Notification to Travel Asst." then adding the e-mail address to the string would be a solution.

for example:
To find the email addresses for "1st Notification to Travel Asst."

        For iCounter = 1 To WorksheetFunction.CountA(Columns(49))
            If Cells(iCounter, 5) = "1st Notification to Travel Asst." Then
                MailDest = Cells(iCounter, 23).Value & ";" & MailDest
            End If
        Next iCounter

 

[Rile1]

Hi Dave

Sorry did not get back earlier, but thanking you for your help

Thanks