Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Emailing from Excel using VBA code

0

Hello,

I have an Excel spread sheet that I would like to create a VBA code to do the following:

1. Send an email to an email address that is in the sheet (column F)

2. The email should be only sent to individuals where a date in the corresponding row is three or more days after "Todays" date.(column H where the date is in purple)

3. I would like the information in the email to be the corresponding row or pulled from data in that row. In addition to a message.

I have been messing around with a VBA code based on one of the videos watched, however it only shows how to send the email to an address in a single cell, not all addresses in a column. I also can't figure out how to send to only those with certain dates or how to data from an individual row be displayed in the email.

Also, I have my column headers on row 2 instead of row 1. is this problem or can i leave my headers as is? 

I look forward to reading responses. 

Answer
Discuss

Discussion

Lisbeth.

When you talk about "certain dates", doe you mean the "Pick up Date" (collumn E in sheet December 2020) or some other date?
John_Ru (rep: 6092) Dec 31, '20 at 2:03 pm
Hi John,
I want this to be for column H - the closing date. I only want the email to be sent to individuals where that date is in purple.
Lisbeth (rep: 2) Dec 31, '20 at 2:09 pm
Add to Discussion

Answers

1

Lisbeth

Please find attached my version of your file. The code below currently creates draft emails for the 26 leads where there's a date in column H which earlier than today (see the first line in bold in the code, a test inside a simple loop)- that's like what your purple conditional formatting does. 

The sub is located in new Module1 so could be run from any sheet (you need to do that manually at present but you could assign it to a button or move it). The first test stops you running it on a new sheet I've added, called EmailBoilerplate (but you can change that without affecting the code and tweak the wording in the cells which get used in the emails).

The code calls on a function called RangetoHTML(Rng As Range) to convert the header and lead row into HTML- that's created by VBA guru Ron de Bruin and available on the interent if you search- I haven't listed it in this answer but credit to him.

Private Sub ReminderEmails()

Dim OutApp As Object
Dim OutMail As Object
Dim n As Integer
Dim SendRows As Range

On Error Resume Next

If ActiveSheet.Name = Sheet1.Name Then ' don't run from boilerplate sheet
    MsgBox "Please select run from data sheet instead"
    Exit Sub
    Else
    MsgBox "Please wait while emails are created (in Outlook's Drafts folder)"
End If

Application.ScreenUpdating = False

Set OutApp = CreateObject("Outlook.Application")

For n = 3 To Cells(Rows.Count, 1).End(xlUp).Row 'check all rows where there's a value in column A

    If Range("H" & n) < Date And Range("H" & n) <> "" Then ' check if it's due or late c.f. today

        Set OutMail = OutApp.CreateItem(olMailItem)
        Set SendRows = Union(Range("A2:I2"), Range("A" & n & ":I" & n)) ' pick header row and lead row

        With OutMail
            .To = Range("F" & n)
            .CC = ""
            .Subject = Sheet1.Range("A4") & Range("C" & n)
            .HTMLBody = RangetoHTML(Sheet1.Range("A10, A13")) & RangetoHTML(SendRows)  'call function to convert rows
            '.Display
            .Save
            '.Send
            .Close
        End With

    'Range("I" & n) = Range("I" & n) + " Reminder email sent " & Date & "."

    End If

Next n

Set OutMail = Nothing
Set OutApp = Nothing

Application.ScreenUpdating = True
MsgBox "Done!"

End Sub

Currently the code just saves a draft but (once you have valid email addresses from row 16 down) you could change the email actions (in bold) to just send, by uncommenting .Send (or . Display if you want them all to appear on screen when created).

The code could then add a note about the emails to the text in column I - you'd need to uncomment the line in bold near the end of the loop: Range("I" & n) = Range("I" & n) + " Reminder email sent " & Date & "."

Hope this is what you need. Happy New Year!

Discuss

Discussion

Hi Lisbeth

Hope this works for you. I notice that someone has voted for my answer (which is nice) but if my answer solves your problem, pleased mark it as Selected rather than voting for it. Either way, thanks in advance.

If you still have a problem, kindly advise.
John_Ru (rep: 6092) Jan 4, '21 at 5:01 am
Thanks so much for your help. I'm going to give this a try and report back.
Lisbeth (rep: 2) Jan 5, '21 at 4:50 pm
Thanks Lisbeth.

Hope it works, it did for me.
John_Ru (rep: 6092) Jan 5, '21 at 5:17 pm
Lisbeth

Was your test successful? Any problems?
John_Ru (rep: 6092) Jan 8, '21 at 3:19 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login