How to create macro for set up an reminder email ms-outlook from the ms-excel in 2007

ashayuva · Sep 22, 2011

Hi,

Can anybody help me out this scenario i am new to macro. I want to create macro for send reminder automatic email from ms-outlook referring the excel spreadsheet with specified data key in to the corresponding email address.
 · 1 Invoice
· 2 PO#
· 3 DELIVER DOC #
· 4 BOL#
· 5 PRO#
· 6 SHIPDATE
· 7 SHIPVIA
· 8 FREIGHT CARRIER NAME
· 9 FREIGHT CARRIER EMAIL ID
· 10 first ST MAIL SENT DATE
· 11 ANSWER RECEIVED
· 12 2<sup>ND</sup> REMINDER MAIL SENT DATE
Above mentioned are the columns in excel tracker. if the answer received column is blank then the reminder email send to the corresponding freight carrier name column 8 and email id will refer column 9 and the database for the frieght carrier name and email address need to refer the another sheet.
the email subject will be reminder email and the content will the column 1 to 8.buttom of the page my name. anyone can help me in this macro will be highly appericiated.
please send me the template or model this mail id asha_yuva11@yahoo.co.in

bensonsearch · Sep 23, 2011

Hi,
The below code is how i create an email

Code:

Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
 
    Dim user As Variant
 
    user = 'your email addresses
 
 
 
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    strbody = 'body of email
    On Error Resume Next
    With OutMail
        .To = ""
        .CC = ""
        .BCC = user
        .Subject = "Subject goes here"
        .body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing

you would need to change the strbody to equal your required fields eg:

Code:

strbody = sheets("Sheet1").range("A1:F5").value

but change as needed. same with the email address, would pul from the sheets as well

ashayuva · Sep 23, 2011

Hi,

Thanks for your code but i need to add one condition here 11th column if not filled then the reminder email need to send automatically if the excel sheet is updated.

how can we drive if that is the case

bensonsearch · Sep 25, 2011

hi,
2 ways if i understand correctly.

1: using sheet update code, this works by monitoring the cell or cells and if changed will do something

2: when you run the macro it tests the cell/s for what you want

number 2 would simply be an if statement like

Code:

if sheets("Sheet1").range("G11").value = "" then
'run email 
else
exit sub
end if

How to create macro for set up an reminder email ms-outlook from the ms-excel in 2007

ashayuva

New Member

Excel Facts

bensonsearch

Well-known Member

ashayuva

New Member

bensonsearch

Well-known Member

Similar threads

Forum statistics

Share this page

How to create macro for set up an reminder email ms-outlook from the ms-excel in 2007

ashayuva

New Member

Excel Facts

bensonsearch

Well-known Member

ashayuva

New Member

bensonsearch

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock