Hello again all, I'm back with another question! Last time you guys were awesome with helping me find a solution, so I hope this question is just as easy for you excel pros.
Ok, so what i'm trying to accomplish is set up a VBA macro that automatically emails me with a list of rows where every row has a Date column that is older than 30 day. I have already accomplished getting the email part to work, based off of examples and help threads from the web and this forum as well. My problem is the Body part of the email. I am having trouble with what I need to include in the module that searches the entire 'F' Column (Expiration Date) and where that date is older than 30 days, it takes the User Name from Column A + the NickName from Column B + the Expiratoin Date from Column F and appends that data to the string that is later used in the "body" part of the email.
Headers From Excel Worksheet (Column Names)
USER: |
Nickname/Comment: |
MAC Addresses: |
# Users: |
Close? |
Expiration Date: |
Excel Module Code:
Option Explicit
Const strTo As String = "myEmail@address.com"
Const strCC As String = "" '<~~ "" if you do not want to CC
Const strBCC As String = "" '<~~ "" if you do not want to BCC
Sub Sample()
Dim OutApp As Object, OutMail As Object
Dim strbody As String, strSubject As String
Dim strbodyStart As String, strbodyEnd As String
strSubject = "!Important - Expiring Licenses Report"
strbodyStart = ""
strbody = ""
strbodyEnd = ""
On Error Resume Next
strbodyStart = "Message Start" & vbCrLf
strbody = Application.WorksheetFunction. _
Date(Range("F3:F").Value <= Date - 30) & vbCrLf
strbodyEnd = "Message End" & vbCrLf
On Error GoTo 0
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = strTo
.CC = strCC
.BCC = strBCC
.Subject = strSubject
.Body = strbodyStart & strbody & strbodyEnd
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Sample Of What The Email Should Look Like:
User 1 |
Bob |
11/12/2012 |
User 2 |
Carl |
11/12/2012 |
User 3 |
Sam |
11/12/2012 |
User 4 |
Tony |
11/12/2012 |
User 5 |
Russell |
11/12/2012 |
Thanks in advance for all your help!
Bookmarks