send Reminder Email to Outlook if cell value reaching before due date

sathishsusa · #1 06-03-2017, 03:33 AM

Hello Experts,

This is my first post in this site i hope it will be very useful to me.

i need to send reminder email to client through outlook, automatically if due date reaches before 30 days and 15 days in expiry date column " F & K" .

Each employee having two cards (Driving card and License card) if any one cards expiry soon before 30 and 15 days then send reminder email to the client and cc to others and date of email send to be register in column "H, I, M, N" of 15 and 30 days.

please refer the attachment for more information the original file and what result i need to be in outlook mail.

please Experts help me to solve this problems it will be very grateful and appreciate

sathishsusa · #2 06-03-2017, 02:48 PM

Please anyone help me to solve this problems.

silentwolf · #3 07-23-2017, 12:44 PM

Hi,
I only be able to give you some starting points to your issue.
I would create in the word document bookmarks first of all. Those bookmarks can then be updated via VBA so your document receives all the necessary informations of your Excel worksheet.
Once you got this sorted you write a code to send it to outlook.
Maybe this get you started and I am sure someone here can give you a code to automatically send it via outlook.

HTH
SW

sathishsusa · #4 07-23-2017, 01:05 PM

Quote:

Originally Posted by silentwolf

Hi,
I only be able to give you some starting points to your issue.
I would create in the word document bookmarks first of all. Those bookmarks can then be updated via VBA so your document receives all the necessary informations of your Excel worksheet.
Once you got this sorted you write a code to send it to outlook.
Maybe this get you started and I am sure someone here can give you a code to automatically send it via outlook.

HTH
SW

Hi silentWolf,

First a Big thanks for you many days the forums became silent on my post but atlest you gave me reply thanks .. okay just starting point please give me.. i try to manage if possible ..

silentwolf · #5 07-23-2017, 01:38 PM

Hi,
You know how to create bookmarks in the word template right?
So you create bookmarks in your wordtemplate call them so they mean something you remember..
Save both of your excel and your word template in the same folder that makes it a little bit easier to find and code.

Ok .. let get started .)

In the word document create a modul call it modPublic
Write code in there

public p_docDO as document

so the first Modul is finished.
then create a second module call it something like modDatei in there u create a prozedur

Code:

Sub UseThisDocument()
Set p_docDO = ThisDocument
End Sub

Create another Modul call it modBookmarks

Write following code

Code:

Property Get Textmarke(strName as string)as string
UseThisDocument
if p_docDO.Bookmarks.Exists(strName)then
Bookmarks=p_docDO.Bookmarks(strName).Range.Text
Else
Textmarke=""
End If
End Property

Code:

Property Let Textmarke(strName as string, strText as string)
dim rngTM as range
UseThisDocument

if p_docDO.Bookmarks.Exists(strName) then
set rngTM=p_docDO.Bookmarks(strName).range
rngTM.Text=strText
rngTM.Bookmarks.add strName
Else
msgbox "This bookmark is missing: " & strName, vbCritical
End if
End Property

So if you have this set up then you have access to the bookmarks you have set up in your word ducument!

If you created a bookmark with the name DO_Email for example you can test your code in the immidate window like so

Textmarke("DO_Email")="office@mynewoffice.co"

and press return you will find that the bookmark DO_Email is now set to office@mynewoffice.co

So the first step is set up

Next step is to get the information from your excel Worksheet into those bookmarks .-)
But that is a different story and for me for today a bit to late but maybe someone else has a bit of time to get you further with this .-)

HTH
SW

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Prevent Outlook 2016 from Send/Receive Email when opening Outlook	lcolson	Outlook	1	10-17-2015 04:58 AM
Outlook macro to check a value of a cell in an attachment and send an email based on that value	ketangarg86	Outlook	13	03-25-2015 07:11 AM
Email reminder of an event in outlook calendar	sam2149	Outlook	0	03-03-2015 05:00 AM
Outlook will not send email	yourforester	Outlook	1	10-04-2013 05:55 AM
Use outlook to send text message to a cell phone	TheVillagesFrank	Outlook	0	06-27-2012 01:16 PM