Hello,

I am a BIG novice to programming in VB and I need help creating a macr
in excel.

Let me try to explain what I am trying to do:

The Macro should do one of the following:

- Send an email to the person in column 7 if the value in column 1
is ="over 60 days"
- Send an email to the person in column 10 if the value in column 1
is = "over 45 days".
- If column 19 is empty, then no email should be sent
- Do this automatically when the spreadsheet is open


In addition to this I want only one email to be sent to each perso
(ie. if it sends out a new email everytime the spreadsheet is open th
people will get annoyed :)). I suppose this could be accomplished b
having the macro also check a hidden column to see if this person ha
been already emailed or not.

I hope you people can help me out. :)

Thanks a lo

--
sa
-----------------------------------------------------------------------
sae's Profile: http://www.excelforum.com/member.php...fo&userid=1538
View this thread: http://www.excelforum.com/showthread.php?threadid=27000

sae wrote in message ...
Hello,

I am a BIG novice to programming in VB and I need help creating a macro
in excel.

Let me try to explain what I am trying to do:

The Macro should do one of the following:

- Send an email to the person in column 7 if the value in column 19
is ="over 60 days"
- Send an email to the person in column 10 if the value in column 19
is = "over 45 days".
- If column 19 is empty, then no email should be sent
- Do this automatically when the spreadsheet is open


In addition to this I want only one email to be sent to each person
(ie. if it sends out a new email everytime the spreadsheet is open the
people will get annoyed :)). I suppose this could be accomplished by
having the macro also check a hidden column to see if this person has
been already emailed or not.

I hope you people can help me out. :)

Thanks a lot

Hi
The macros below use Outlook. You will need to set a reference in your
workbook to the Outlook 10 object library. Within the VBE, go to
Tools, References and check the OutLook 10.0 Object Library. You might
be able to get away with OutLook 9.0 or less, I don't know.
Your major adaptation will probably be in defining the CustomerInfo
variable, and the subsequent looping. Get back if you need help with
this.


'This section contains the macros required to integrate with Outlook
'Put these 5 lines at the top of a code module
Option Explicit
Option Base 1
Public objOutlook As Outlook.Application
Public objNamespace As Outlook.NameSpace
Public OutlookWasRunning As Boolean

'Interface with Outlook objects required will be through this sub.
'It is called by any sub requiring access to Outlook.
Public Sub DeclareOutlookObjects()
Application.ScreenUpdating = False
On Error Resume Next
Err.Clear
'If Outlook is already open, flag it with Boolean
Set objOutlook = GetObject(, "Outlook.Application")
If Err.Number < 0 Then
OutlookWasRunning = False
Else
OutlookWasRunning = True
end if
On Error GoTo 0
Err.Clear 'keep err tidy
If Not OutlookWasRunning Then
Set objOutlook = CreateObject("Outlook.Application")
End If
Set objNamespace = objOutlook.GetNamespace("MAPI")
objNamespace.Logon
End Sub

'Releases Public variable objects after Outlook session
Public Sub ReleaseOutlookObjects()
Application.ScreenUpdating = False
objNamespace.Logoff
Set objNamespace = Nothing
If Not OutlookWasRunning Then objOutlook.Quit
Set objOutlook = Nothing
End Sub

'macro to send your email
'I'm assuming columns 7 and 10 contain email addresses

Public Sub CreateMyMail()
Dim objMail1 as Outlook.MailItem, objMail2 as Outlook.MailItem
Dim CustomerInfo as Variant
Dim EmailString60 as String, EmailString45 as String
Dim i as long
'I'm assuming your criteria data is on a worksheet called
"CustomerData"
'Cells(2,1) is then the second row of the 1st column
'Range is resized for 100 rows and 20 columns
'Column 20 is assumed to store info about email already sent
CustomerInfo = Worksheets("CustomerData").Cells(2,1).Resize(100,2 0).Value

For i = 1 to 100 'each Customer
'get the required email addresses, assumed to be in columns 7 and
10
If CustomerInfo(i,20) < "Email Sent" then
If CustomerInfo(i, 19)= "Over 60 Days" Then
EmailString60 = EmmailString60&CustomerInfo(i,7)&";"
ElseIf CustomerInfo(i,19) = "Over 45 Days" Then
EmailString45 = EmmailString45&CustomerInfo(i,10)&";"
end if
CustomerInfo(i,20) = "Email Sent"
End if
Next i

DeclareOutlookObjects 'link to Outlook
'Probably no need for 2 objects?
Set objMail1 = objOutlook.CreateItem(olMailItem)
Set objMail2 = objOutlook.CreateItem(olMailItem)
With objMail1
.BCC = EmailString60
.Subject = "Over 60 Days Heading"
.Body = "Blah blah blah"
.Send
End With
With objMail2
.BCC = EmailString45
.Subject = "Over 45 Days Heading"
.Body = "Blah blah blah"
.Send
End With
ReleaseOutlookObjects
'Put back the info about whether the Mail was sent
'You might have to deal with case of email cancelled
Worksheets("CustomerData").Cells(2,1).Resize(100,2 0).Value =
CustomerInfo

Set objMail1 = Nothing
Set objMail2 = Nothing
End Sub

You can call this sub from your WorkBook_Open event in the
ThisWorkBook module

Private WorkBook_Open()
CreateMyMail
End Sub

Note1: that OutLook will not allow macros to send emails, due to the
virus risk. You must click the final confirmation of send yourself.
Alternatively, you can use something called CDO, which does not use
Outlook and does not give warnings. Look in one of the Outlook
Newsgroups.
Note2: I used the BCC field rather than To above, so that customers
won't see each others email message. Otherwise, you would have to
construct a different email for each Customer.

regards
Paul