Export Excel Data to an Outlook Email

[Guest]

Sounds simple but this is proving a headache for me. I'm looking for any
smnippest of code out there that can handle the following.

In column A I have the date an email should be sent.
In Column B I have the email address to which the email is to be sent.
In Column C I have the subject of the email
In Column D I have the body of the email.
In Column E I have the name of the person sending the email.

The code I'm looking for needs to work like this.

If I click on any cell in column A (on any date), nothing must happen,
unless the date is 'today'. If the datre is today this will trigger the data
in columns b,c,d,e to be entered into an email launched from Outlook in the
roder highlighted below.

If there are any clever boffins out there please help me...

Gordon

 

[Ron de Bruin]

Hi Gordon

Start here
http://www.rondebruin.nl/mail/folder3/message.htm

In the yes/no column use a formula like this
=IF(C1= TODAY(),"Yes","No")

See also tje tips
http://www.rondebruin.nl/mail/tips2.htm

 

[Guest]

Thanks for that...

Have you got a working example of this code...

Gordon

 

[Ron de Bruin]

Hi Gordon

Try this, use display in the code to test

'In column A I have the date an email should be sent.
'In Column B I have the email address to which the email is to be sent.
'In Column C I have the subject of the email
'In Column D I have the body of the email.
'In Column E I have the name of the person sending the email.
'In column F copy down this formula
=IF(A1= TODAY(),"Yes","No")


Sub TestFile()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "*@*" And LCase(cell.Offset(0, 4).Value) = "yes" Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = cell.Value
.Subject = cell.Offset(0, 1).Value
.Body = cell.Offset(0, 2).Value
.Send 'Or use Display
End With
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

 

[Guest]

Thanks Ron...

Hi Gordon

Try this, use display in the code to test

'In column A I have the date an email should be sent.
'In Column B I have the email address to which the email is to be sent.
'In Column C I have the subject of the email
'In Column D I have the body of the email.
'In Column E I have the name of the person sending the email.
'In column F copy down this formula
=IF(A1= TODAY(),"Yes","No")


Sub TestFile()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "*@*" And LCase(cell.Offset(0, 4).Value) = "yes" Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = cell.Value
.Subject = cell.Offset(0, 1).Value
.Body = cell.Offset(0, 2).Value
.Send 'Or use Display
End With
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub