PDA

View Full Version : open workbook - check date and percentage - send email



fabrecass
01-24-2013, 02:39 AM
Hi guys,

New to the forum and hoping someone can help me it's doing my head in!

this is very complicated (for me) and i can't get it right.

Column F is full of product types, column A is full of dates, Columns B and C have numerical values, Column D is full of percentages, column E has email addresses.

so, here's what i need to happen;

Upon opening the workbook, I need to check the dates in column A, if the date is in the past AND column D is NOT at 100%, I need excel to send an email for each row with an expired date to the corresponding email addresses in column E.

Now I think this is the super tricky part - i need the body of the email to state the result of the value of column B minus the value of column C, and the subject to state what is in column F.

Hope someone can help?

I can post what i have so far if that would help?

CodeNinja
01-25-2013, 02:32 PM
Fabrecass,
I am sure others have more experience in this than I do, but this is what I would do....

First you will have to add the reference "Microsoft Outlook 14.0 Object Library". In the VBE, go to tools, references, find it and click ok....


Next, In the workbook_open() routine of thisWorkbook, I would put the following code:


Sub SendEMail()
'Upon opening the workbook, I need to check the dates in column A, if the date is in the past AND column D is NOT at 100%,
'I need excel to send an email for each row with an expired date to the corresponding email addresses
'in column E.
Dim sMessage As String
Dim sSubject As String
Dim sAddress As String
Dim l As Long

For l = 2 To Sheet1.Range("A65536").End(xlUp).Row
If Sheet1.Cells(l, 1) < Date And Sheet1.Cells(l, 4) <> 1 Then
sMessage = "Dear deadbeat I have to write this message to," & vbLf & _
"... blah blah blah..." & Sheet1.Cells(l, 2) - Sheet1.Cells(l, 3) & " ... Yours truly," & _
vbLf & "CodeNinja."
sSubject = Sheet1.Cells(l, 6)
sAddress = Sheet1.Cells(l, 5)
Call SendEMailNow(sMessage, sSubject, sAddress)
End If
Next l

End Sub

Private Sub SendEMailNow(sMessage As String, sSubject As String, sAddress As String)
Dim olApp As Outlook.Application
Dim olNs As Outlook.Namespace
Dim olMail As Object
'
Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.to = sAddress
.Subject = sSubject
.Body = sMessage
.send
End With

Set olMail = Nothing
Set olApp = Nothing

End Sub



I am sure others know a lot more about how to make sure your outlook is open or closed etc and fancy stuff to do, but I think this should work for you for the most part, at least be a good start.

Good luck

CodeNinja.

fabrecass
01-28-2013, 01:24 AM
thank you sir!