personalised emails

taj002

New Member
Joined
Dec 5, 2018
Messages
4
Hello,

I have a code that sends out an email to employees once materials have expired. I am trying to personalize the email so that it says "hello (name!)" then later in the body I want it to list out the information about their materials that are expiring. Below is the information based on the columns that I have it in and this is the info I want to be displayed in the body of an email (further below is my entire code). If anyone can modify my code to display this information i would greatly appreciate it! Thanks

Code:
mail_body_message = ("L5")
full_name = ("H")
kit_quantity = ("D")
kit_type = ("C")
protocol = ("A")
protocol_number =("B")
expiration_date = ("E")


CODE:

Sub SendReminderMail()
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
MailDest = ""
For iCounter = 1 To WorksheetFunction.CountA(Columns(7))
If MailDest = “” And Cells(iCounter, 7).Offset(0, -1) = "EXPIRING OR EXPIRED" Then
MailDest = Cells(iCounter, 7).Value
ElseIf MailDest <> "" And Cells(iCounter, 7).Offset(0, -1) = "EXPIRING OR EXPIRED" Then
MailDest = MailDest & ";" & Cells(iCounter, 7).Value
End If
Next iCounter
 
.BCC = MailDest
.Subject = "Expiring Kits"
.Body = "Hello replace_name_here! This is just a reminder that there are some kits that are about to expire in your study. Be sure to arrange any necessary reordering that you need to. I will be pulling the boxes from the shelf and destroying them upon expiration. Please let me know of any concerns. Thanks! Trent"
.Send
End With
 
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
End Sub
 
Last edited by a moderator:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hi - Welcome to the Forum

Assuming your emails are formatted in the normal way (like jazz.sp8@mrexcel.com)

Then try;

Code:
Sub SendReminderMail()
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
    MailDest = ""
    For iCounter = 1 To WorksheetFunction.CountA(Columns(7))
        If MailDest = “” And Cells(iCounter, 7).Offset(0, -1) = "EXPIRING OR EXPIRED" Then
        MailDest = Cells(iCounter, 7).Value
        PersonName = WorksheetFunction.Proper(Left(Cells(iCounter, 7).Value, WorksheetFunction.Find(".", Cells(iCounter, 7).Value) - 1))
    ElseIf MailDest <> "" And Cells(iCounter, 7).Offset(0, -1) = "EXPIRING OR EXPIRED" Then
        MailDest = MailDest & ";" & Cells(iCounter, 7).Value
        PersonName = WorksheetFunction.Proper(Left(Cells(iCounter, 7).Value, WorksheetFunction.Find(".", Cells(iCounter, 7).Value) - 1))
    End If

    Next iCounter
 
    .BCC = MailDest
    .Subject = "Expiring Kits"
    .Body = "Hello" & PersonName & "! This is just a reminder that there are some kits that are about to expire in your study. Be sure to arrange any necessary reordering that you need to. I will be pulling the boxes from the shelf and destroying them upon expiration. Please let me know of any concerns. Thanks! Trent"
    .Send
End With
 
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
End Sub

If that isn't the case then if you could let us know how the names are formatted in your worksheet then that'd be good :)
 
Upvote 0
Thank you, but I want to add the info about the name, protocol, protocol number, kit type, and expiration date as well. Will this follow the exact same format as what you did for the name? Could you maybe add that info into my code based on their respective columns as I have shown above? Thank you!
 
Upvote 0
Hello,

I am getting a typed mismatch error with the PersonName = WorksheetFunction.Proper(Right(Cells(iCounter, 7).Value, WorksheetFunction.Find(".", Cells(iCounter, 7).Value + 1))) line. My names are in column H, to the right of the column with the emails in them. Any idea what this mismatched error is about? Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top