sending email from outlook using vba excel

Dear All,

I am new to the forum. kindly help me in getting VBA codes for sending emails by collecting information from excel (Column A- date, B - recipient id,C subject, D - cc, E - message body) as and when current date is equals to the Date in Column A


E.G -

A-------------------------- B---- C------------- D------------------- E
Date Recipient id Subject cc Message Body
28/07/2012 me@me.com report u@gmail.com report for the month..
28/07/2012 me@me.com report u@gmail.com report for the month..
30/07/2012 me@me.com report u@gmail.com report for the month..
31/07/2012 me@me.com report u@gmail.com report for the month..
01/08/2012 me@me.com report u@gmail.com report for the month..
02/08/2012 me@me.com report u@gmail.com report for the month..
03/08/2012 me@me.com report u@gmail.com report for the month..
04/08/2012 me@me.com report u@gmail.com report for the month..


Thanks in advance
Josh

Hi Josh
Welcome to Excel Forum. Please when asking a question such as yours supply a sample workbook rather than placing the data into the thread as it make supplying an answer easier otherwise the person offering the solution may need to recreate a workbook to reflect your data. To add a sample workbook select "Go Advanced", "Manage Attachments" and upload a sample workbook. Make sure the sample workbook contains no sensitive or private data.
Based on your explanation the following should send an email if the current date matches the date in Column A. If it does not help you then please supply a sample workbook. In this case I assume the data starts in Row 2. I have placed some comments into the code to help you where changes may need to be made depending on your workbook layout. This code will display the email - change Display to Send where indicated to just send the email.

Sub sendEmail()
Dim OutApp As Object
Dim OutMail As Object
Dim dataWs As Worksheet
   On Error GoTo test_Error
'Change the following line to the sheet containing the data
Set dataWs = Worksheets("Sheet1")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
For k = 2 To dataWs.Cells(Rows.Count, "A").End(xlUp).Row
If CDate(dataWs.Range("A" & k)) = Format(Now, "dd/mm/yyyy") Then
With OutMail
    .To = dataWs.Range("B" & k)
    .Cc = dataWs.Range("D" & k)
    .Subject = dataWs.Range("C" & k)
    .Body = dataWs.Range("E" & k)
     ' In place of the following statement, you can use ".Send" to Send the email
    .Display
     '.Send
End With
End If
Next
Set OutMail = Nothing
Set OutApp = Nothing
   On Error GoTo 0
   Exit Sub
test_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure test of  Module1"
End Sub

If you have any questions please ask.

Thank you for your help, and i tried uploading the file when i made this post but i had issues uploading..

But i have one more question to ask

how to send the email if there are 2 recipients on the same date, with diff subj and body..

Thanks in advance

attachment : example.xlsm

The code I wrote above will iterate through all the dates and if there are multiple emails to send with the same date does the code not do what you require? What happens with the code when there are multiple emails for the same date - does the code not work in that situation?

Hi Anthony, thank you so much for the valuable input, however the code when tried is sending only one email ( the first one in the list, which is current date ) while the other current dated ones are ignored. request your help.

Thanks

Josh

The code needs to be changed a little - try the following

Sub sendEmail()
Dim OutApp As Object
Dim OutMail As Object
Dim dataWs As Worksheet
   On Error GoTo test_Error
'Change the following line to the sheet containing the data
Set dataWs = Worksheets("Sheet1")
For k = 2 To dataWs.Cells(Rows.Count, "A").End(xlUp).Row
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
If CDate(dataWs.Range("A" & k)) = Format(Now, "dd/mm/yyyy") Then
With OutMail
    .To = dataWs.Range("B" & k)
    .Cc = dataWs.Range("D" & k)
    .Subject = dataWs.Range("C" & k)
    .Body = dataWs.Range("E" & k)
     ' In place of the following statement, you can use ".Send" to Send the email
    '.Display
     .Send
End With
End If
Set OutMail = Nothing
Set OutApp = Nothing
Next
   On Error GoTo 0
   Exit Sub
test_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure test of  Module1"
End Sub

This will SEND every email that matches today's date

Thanks for the great posts guys, trying to do something similar using RDB's coding but it keeps coming up with compile errors.

Is it possible to tweak the basic code to compile a pdf of cells B1:I56 and send to recipient in L4 on button activation using default email program?

Having a lot of trouble combining the VBA sections and every time I get compile errors.

My main goal is to have the participant value as a listbox with all sections filled out in vlookup with a simple macro button to compile and send through the default mail with a standardised subject and filename.

I should also note, I'm editing this in Excel for Mac 2011 and want it to be compatible with older versions of excel on PC's

Hi and welcome to Excel Forum - I would ask that you read our forum rules at http://www.excelforum.com/forum-rule...rum-rules.html
Please don't post a question in the thread of another member -- start your own. If you feel this thread is particularly relevant to your problem then provide a link to the this thread. If you repost your question in a new thread I am sure you will receive the help that you require.