Reminder /follow up macro connecting excel to outlook

[amlan009]

This is gonna be a tough one even for the expert's ,luckily i have a few lines of codes ready which can help as we take on this project .Basically ,am trying to make a macro which connects excel to outlook and then sends mail only on specified intervals userdefined by a formula in excel sheet .


Case :Say my girlfriends birthday is on 30/02/2012 ,i wanna capture this date in cell C2 ,I wanna capture a start date from which i wanna keep reminding myself and a few friend's in cell D2 ,set up frequency in cell E2 (at what intervals from start date i wanna send myself and her friends the reminder's ,this reminder will start going from start date to the target date in frequency mentioned above , and finally cell F2 for time



Now comes the important part ....followup ...i want the condition to be checked in cell G2 TO see whether condition of followup is applicable by a simple yes or no statement .if the cell is filled yes ,then even post target date mails will be sent to target reciptent for following up ,if cell is selected no then this part will not be carried out .......

if followup is selected ,then again frequency ,time etc in cells H and I note this mails shall be now send post target date



Eg:,say i forget her birthday ....i send myself followup mails to do something every 3rd day to make her feel good (every 3rd day is the frequency to be mentioned in cell H and time say 3.00 pm everyday to be mentioned in cell I ,
Eg:If i dont forget ,i simply type no in cell G2 ,




Cell I2 is for a stop function ,lest we type stop here ,all processes in that row will now not take place ,if we type resume here ,it will continue back to normal ,


Attachment ,subject ,cc ,bcc ,body in J2,K2,L2,M2,N2 rows respectively ,

Also in P2 a button macro for preview /and a seperate button macro for sending without preview (Calling outlook .Display &.send seperately using 2 macros ,we can then press any of the either macros to initiate our case )




In Cell A2 ,send option,(This will play a role when we have multiple rows .....maybe A2 i will type yes ,B2 no ,C2 yes ,then for specific incidents defined in A2 and C2 ,mails will be sent as per condition's prespecified ,





In cell B2 ,say this is the active sheet i am working on ,i might need to attach other sheets of the same workbook ,so is it possible that i type in sheet name (i saw something similar in Ron de bruin plugin ),but i wanna solve it myself for educational purpose and benefitting my firm ,

In cell o2 ,i mention the filename which shall get prefixed to the sheet selected ,


In cells pertaining to sheet 's, email id ,cc,bcc ,attachment ,i can select as many as i want by pressing alt+enter ,


I undersrand that for such macro the outlook and excel has to open when target date and time arrives since it is a PST application (i dunno what my professors mean when they say that ),but is there a way to overcome this ,


This is gonna be a tough nut to crack ,but its the thought which is difficult ,just few days back ,i had this code below which connects excel to outlook and does similar things except handling the date and time issue ..the values are in different cells ,for a macro expert this will make sense and the loop continues until the email adresses are contained in the rows ,

Here's the code i saw somewhere else in this same blog to which i did some minor modifications to suit my other excel sheet ,basically email adresses are contained in Column B ,first being B2 ,subject I2,L2(CC),Attachment path at J2

Sub SendMailwithoutpreview()
I = 2
Do

MyFile = Cells(I, 10).Value
Subj = Cells(I, 9).Value
EmailTo = Cells(I, 2).Value
CCto = Cells(I, 12).Value
User = Cells(I, 11).Value
msg = Cells(I, 13).Value
'"Dear " & User & vbNewLine & " Please find attached your audit trail" & " Kind regards " & vbNewLine & vbNewLine & Application.UserName
Workbooks.Open Filename:="C:\Documents and Settings\Administrator\Desktop\" & MyFile, UpdateLinks:=False

Application.DisplayAlerts = False

Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = EmailTo
        .CC = CCto
        .BCC = ""
        .Subject = Subj
        .body = msg
        .Attachments.Add ActiveWorkbook.FullName
        .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
Application.DisplayAlerts = False
Workbooks(MyFile).Close
Application.DisplayAlerts = True

I = I + 1
Cells(1, "G").Value = "Outlook sent Time,Dynamic msg sent count  =" & I

Loop Until Cells(I, "B").Value = ""


End Sub

I saw another code in same thread which showed how to preview mail ,

here's the code ,basically email adresses are contained in Column B ,first being B2 ,

Sub previewmails()
I = 2
Do

MyFile = Cells(I, 10).Value
Subj = Cells(I, 9).Value
EmailTo = Cells(I, 2).Value
CCto = Cells(I, 12).Value
User = Cells(I, 11).Value
msg = Cells(I, 13).Value
'"Dear " & User & vbNewLine & " Please find attached your audit trail" & " Kind regards " & vbNewLine & vbNewLine & Application.UserName
Workbooks.Open Filename:="C:\Documents and Settings\Administrator\Desktop\" & MyFile, UpdateLinks:=False

Application.DisplayAlerts = False

Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = EmailTo
        .CC = CCto
        .BCC = ""
        .Subject = Subj
        .body = msg
        .Attachments.Add ActiveWorkbook.FullName
        .Display
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
Application.DisplayAlerts = False
Workbooks(MyFile).Close
Application.DisplayAlerts = True

I = I + 1

Cells(1, "G").Value = "Outlook sent Time,Dynamic msg preview  count  =" & I

Loop Until Cells(I, "B").Value = ""


End Sub

So i saved this like shown above in separate modules and in active sheet create 2 macro buttons one for previewing and one for sending ,


I yet have to figure out the date ,time for reminder and followup ,integrate that in this and also account for the capability to attach specific sheets of the same workbook,using above code we can send to multiple email id ,multiple cc ,Multiple BCC ,doing alt +enter


I know this is gonna be interesting and look forward to learn /participate and actually understand the solution as we proceed to solve this case together ,


Humble Regard's,


Amlan Dutta


"life is always a learning curve "

 

[amlan009]

I see noone could attempt this ,anyways ,i am working on it and will soon get back with a solution ,


Humble Regards ,


Amlan Dutta

 

[amlan009]

i think the code is self explanatory ,i am developing it myself and it works like a dream ,now my toughest part will be to account to use a for loop from start date to end date with a step (frequency ) so that using deferred vale of outlook ,i can set out to achieve exactly what i want

Sub remindermails()
I = 3
Do

SenderName = Cells(I, "C").Value
Event1 = Cells(I, "B").Value
Subj = Cells(I, "L").Value
EmailTo = Cells(I, "D").Value
Filepath = Cells(I, "M").Value
Startdate = Cells(I, "F").Value
Startdate = CDate(Startdate)
Enddate = Cells(I, "E").Value
Enddate = CDate(Enddate)
Requestdate = CDate(Enddate) - 2   '2 days before event date
Remainingdays = Enddate - Startdate
CCto = Cells(I, "K").Value
'User = Cells(I, 11).Value
msg = Cells(I, "N").Value

Application.DisplayAlerts = False

Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = EmailTo
        .CC = CCto
        .BCC = ""
        .Subject = Event1 & "" & Subj
        .body = "Dear " & SenderName & vbNewLine & vbNewLine & "You Have " & Remainingdays & " day/days  remaining for the  subject event " & vbNewLine & vbNewLine & " Please do the needful latest by " & Requestdate & vbNewLine & msg & vbNewLine
        .Attachments.Add Filepath
        .Display
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing

Application.DisplayAlerts = True

I = I + 1

Cells(1, "E").Value = "Outlook sent Time,Dynamic msg preview  count  =" & I - 3

Loop Until Cells(I, "D").Value = ""


End Sub

I would love someone to help ,but then there is no greater pleasure in life than solving by self ,and i will shortly do it ,i know this is a very popular item in many forum's ,so my posted solution,if it works , will help many ,


Humble Regard's,


Amlan Dutta

 

[JP2112]

LOL you do this the hard way, I just find a website that sends you reminders

Or I set up a task reminder X days before the event: http://www.jpsoftwaretech.com/using-excel-vba-to-set-up-task-reminders-in-outlook/

 

[amlan009]

Thank's,i found the code in that url link a bit too complex for a newbie like me to understand wit so much commenting ....i find my own code very easy(Maybe it's because i develop it and we often love the one's we develop ) ...i am still on process of developing it .....in the meantime your code in that website link does open doors to a few thing's i was not aware of ,so thanks so much ,


Humble Regard's,


Amlan Dutta