Emails from Excel (via VBA)

Rile1

New Member
Joined
Aug 7, 2018
Messages
6
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
123452223
DateStatusTravel Asst. NameBooking Asst. NameStatusTravel Asst. EmailTravel Manager Email
27-Aug-18Pending BookingABC123Escalation to Travel Managerabc@gmail.com123@gmail.com
27-Aug-18Pending Presentation DEF4561st Notification to Travel Asst.yxz@gmail.com345@gmail.com
27-Aug-18Pending PresentationZXY7892nd Notification to Travel Asst.abc@gmail.com123@gmail.com
27-Aug-18Pending PaymentKLM101Escalation to Booking Manageryxz@gmail.com345@gmail.com

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,015
Your code does indicate the ".to:" address
 
Last edited:

Rile1

New Member
Joined
Aug 7, 2018
Messages
6
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

Well-known Member
Joined
Feb 26, 2006
Messages
1,015
Code:
 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."
Code:
        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

New Member
Joined
Aug 7, 2018
Messages
6
Hi Dave

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

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,108,584
Messages
5,523,720
Members
409,532
Latest member
Lmfacc

This Week's Hot Topics

Top