VBA advice

[LisaContractorUmbrella]

Morning chaps.

I am trying to automatically send out emails based on a list contained in Excel - I have written the code which will send the email but what I can't work out how to do is send it only when certain conditions are met i.e. when the date shown in the spreadsheet is today's date.

Spreadsheet format is basic with 5 columns but variable rows - name, email address, text column and then 2 date columns - I need code to send automated email to email address where the date in the corresponding row of the date column is today.

Hope that makes sense - any advice much appreciated

[northernladuk]

Could you supply an address to send our invoices to and what are your payment terms?

[LisaContractorUmbrella]

Could you supply an address to send our invoices to and what are your payment terms?

Yep certainly can and payment when my problem is fixed So can you fix the problem and how much are you going to rush me NLUK??

[DirtyDog]

Where is the email content defined? Is it in Excel as well, or are you trying to read the records from Excel into something else to compose and send the email? How many names are you talking about doing?

[pjclarke]

Excel dates are being stored as a number. With the integer particle is holding the date and the time is being as a decimal, so you must comparing the integer part of date cellular with the integer part of current date/timing. Isn't It?

Assuming your dates are coming in column 5 (or 'E') and mail addresses column 2 ('B') and the address data most correctfully is in rows 4 to 20, in a worksheet being named 'Data', something like:


Sub SendSomeMail()

Const DATECOL = 5
Const MAILCOL = 2
Dim Row As Integer
Dim EmailAddress As String
Dim DataSheet As Worksheet

Set DataSheet = Worksheets("Data")
For Row = 4 To 20
If Int(DataSheet.Cells(Row, DATECOL).Value) = Int(Now()) Then
EmailAddress = DataSheet.Cells(Row, MAILCOL).Value
'' Mail Function here
End If
Next 'Row

End Sub

Needful done. Payment to Shawaddidi Consulting, Jersey.

Many thankings.

[LisaContractorUmbrella]

Where is the email content defined? Is it in Excel as well, or are you trying to read the records from Excel into something else to compose and send the email? How many names are you talking about doing?

Email address is contained within the spreadsheet - list will be updated regularly - each batch of emails will be around 20-30

[pjclarke]

In which case, if the length of the list may vary, you'll need to add some code to work out the end value for 'Row'.

PM me for a quote ;-)

[northernladuk]

Yep certainly can and payment when my problem is fixed So can you fix the problem and how much are you going to rush me NLUK??

Busted. I don't even know what VBA stands for let alone fix it

[DirtyDog]

Email address is contained within the spreadsheet - list will be updated regularly - each batch of emails will be around 20-30

Where is the body of the email - is it also in Excel?

If the email contents changes, then I would use Excel to generate a list of the email addresses to send to, separated by a comma, and then copy that into your email program as the CC/BCC list.

eg.
Col A = email addresses
Col B = date
Col C = =if(B=TODAY(),A,"")

Then have a function to concatenate all the values in C into one field, and copy that list of email addresses into Outlook (or whatever)

[LisaContractorUmbrella]

Busted. I don't even know what VBA stands for let alone fix it

Awww bless ya