PDA

View Full Version : auto email generation



rajagopal
05-24-2007, 01:42 AM
The cell d8 has user name, d9 has date.
When the date mentioned in cell d9 arrives, the system should send auto email using outlook to the user name mentioned in cell d8 with some text message.
Can it be done using macros which can be executed by itself. (auto execution of macros)

Bob Phillips
05-24-2007, 02:01 AM
Private Sub Workbook_Open()
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object

With ThisWorkbook.Worksheets("Sheet1")
If .Range("D9").Value = Date Then

Set oOutlook = CreateObject("Outlook.Application")

Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True

Set oMailItem = oOutlook.CreateItem(0)
Set oRecipient = oMailItem.Recipients.Add(.Range("D8").Value)
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
With oMailItem
.Subject = "Automatic notification."
.Body = "This is an automatic email notification"
' .Attachments.Add ("filename") 'you only need this if
'you are sending attachments?
.Send
End With

Set oRecipient = Nothing
Set oMailItem = Nothing
Set oNameSpace = Nothing
Set oOutlook = Nothing
End If
End With

End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

rajagopal
05-24-2007, 03:44 AM
The email has to be sent when the file is not opened.i.e. if i mention the target date as 26/05/2007, the email has to be sent when the system date changes from 25/05/2007 to 26/05/2007.

rajagopal
05-24-2007, 03:56 AM
I've placed the codes in View code link when right clicked the Excel icon. I updated cell D60 with contents, j60 with user name, l60 today's date, saved the file and closed.
when i opened the file, the emails are not generated.
Please clarify.

rajagopal
05-24-2007, 05:02 AM
Outlook should search the address list with the user name mentioned in cell j60 and place that email id in the TO: address list.

Veekay2007
05-25-2007, 09:31 AM
You can try to use the Windows Address Book / Outlook Address Book automation features for checking the existance of the user, before placing it in the TO field.

HTH
Veekay

lucas
05-25-2007, 09:37 AM
The email has to be sent when the file is not opened.i.e. if i mention the target date as 26/05/2007, the email has to be sent when the system date changes from 25/05/2007 to 26/05/2007.

I don't think you can do this.....not without scheduling the file to open on a certain date by some other means.

rajagopal
05-27-2007, 10:37 PM
If it is not possible without scheduling the file to open, Can it be done when the system is used by the user.
I need the codes for the same.
Please help.

rajagopal
05-29-2007, 09:44 PM
Please provide the codes for auto email generation.

lucas
05-30-2007, 05:32 AM
see post #2

rajagopal
05-30-2007, 05:45 AM
It didn't work.
See the attached file.
when system date matches with the date in cell range R7, the email has to be sent to the user name metioned in cell O7.
We're using MS outlook.
Please help.

lucas
05-30-2007, 06:00 AM
The code is not in the workbook...for one thing. I would suggest that you get away from the merged cells..it will cause you problems when working in vba.

The code from post 2 has to be adjusted to your sheet and be located in the thisworkbook module...see attached.