If-Then statement for keeping a follow-up date sending reminder e-mails. (1 Viewer)

[ledmark]

Hello - I have a form that has a field where a follow-up date is entered and an e-mail is generated based on that date to send a reminder e-mail. I have also added a field call FUComplete, which is a yes/no field. I want to have an If-Then statement say "If FUComplete = True then do nothing but If FUComplete = False then [ProjectDate] + 8 days (so the coding will add 8 calender days to the ProjectDate and put it in the FUDateContacted field and generate an e-mail that will be sent again in 8 days). I'd like to do this for the FUComplete field for 8 days past ProjectDate, 12 days, 16 days, 20 days and 24 days. It would also have to look in the table to see who the original e-mail recipient was and put that e-mail name in the EmailRecipient field.

This is the coding that sends the initial reminder e-mail based on the FUDateContacted field:


Private Sub FUDateContacted_AfterUpdate()

Dim appOutLook As Object
Dim MailOutLook As Object

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

With MailOutLook
.To = Me.EmailRecipient 'get the recipient from the form
.Subject = "Project ID:" & Me.ID & ", Street Ref: " & Me.ClientStreet 'get the street and ID from the form
.body = "This is a reminder that you need to make a follow up call to the address in the Subject line today."
.DeferredDeliveryTime = Me.FUDateContacted 'get the date from the form

.Send
End With

Set appOutLook = Nothing
Set MailOutLook = Nothing

End Sub


Is it possible to put the coding for the if/then statements (if this would be the way to do it) in this existing coding? I would so much appreciate help with this.

If this is not possible will someone please tell me up front so I don't keep posting it? Thank you!!

Thank you!!
Laura

 

[Beetle]

Well, if FUDateContacted is the field in which you want to set a value using code, then you shouldn't run this code in the AfterUpdate event of FUDateContacted, because the AfterUpdate will only fire if a user enters the value, so you use a command button (or some other event) to do this.

Here is some example code;

<code>

If Not FUComplete Then 'project is not complete
If IsNull(Me!FUDateContacted) Then 'no email has been sent yet
Me!FUDateContacted = DateAdd("d", 8, Me!ProjectDate) 'add 8 days to Project Date
Else 'initial email has been sent so add 4 days to Date Contacted
Me!FUDateContacted = DateAdd("d", 4, Me!FUDateContacted)
End If

Dim appOutLook As Object
Dim MailOutLook As Object

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

With MailOutLook
.To = Me.EmailRecipient 'get the recipient from the form
.Subject = "Project ID:" & Me.ID & ", Street Ref: " & Me.ClientStreet 'get the street and ID from the form
.body = "This is a reminder that you need to make a follow up call to the address in the Subject line today."
.DeferredDeliveryTime = Me.FUDateContacted 'get the date from the form

.Send
End With

Set appOutLook = Nothing
Set MailOutLook = Nothing

End If

</code>

 

[ledmark]

OK - I'm a little confused. I looked in the table and it is not storing the e-mail address when it is entered. The clerk enters the sales person's e-mail address from a list box then enters the FUDateContacted (this being the date the sales person is supposed to call the client). If they have done the follow-up call they will check the box next to FUComplete so it is "true".

I'm trying to see what the coding is saying that you entered - if FUComplete is not checked the project is not complete. If the follow up date field has nothing in it then no e-mail was sent - but, there will always be a follow up date there because the clerk enters a date that is 4 days after the ProjectDate. Should the adding the eight days be based on the FUComplete field - whether it's true or false? If true then send no e-mail and if false add 8 days to the project date, which is stored in the FUDateContacted field?

Then it looks like it is saying that if the initial e-mail was sent but the FUComplete box is not checked then add 4 days to the FUDateContacted field?

What am I missing in having the e-mail stored in the table? It is set up like this in the ClientInfo Table:

Combo Box
Table/Query
SELECT [tblEmail].[EmailID], [tblEmail]. FROM tblEmail;

Should I have the Control Source for the EmailRecipient field on the form set to the EmailRecipient field in the table?

 

[Beetle]

OK, so there are two separate issues here. First, your problem with the customer email.

Just to clarify, you said the user is selecting the email from a list box but then later you mention a combo box. Not much of a difference but I just want to be clear on what we're talking about. Now to the real issue. If I understand correctly, you are using a combo box in the table itself. I recommend against this for a few reasons, the main one being that it misleads you about what is actually being stored in the table. In your case it looks like it is actually storing the EMailID, not the actual EMail address (it is only displaying the EMail address via the combo box). I assume you have tblEMail set up to handle multiple e-mails for the same customer, so you're going to have to grab the appropriate EMail address directly from tblEMail, not from the Customers table. You can find more info on Lookup fields (combo boxes in tables) at this link;

http://access.mvps.org/access/lookupfields.htm

Next, the code for sending an e-mail.

If the FUDateContacted is always going to have an initial value (entered by the user) of 4 days from the project date, then all you need to do is look at this field (FUDateContacted) and add 4 days (which would give you a starting point of 8 days from the project date). This only requires a slight modification to the previous code, an updated example is posted below. Basically, this code checks to see if the FUComplete field is False. If so it checks the date value in the FUDateContacted field, adds 4 days, then processes the code to generate an e-mail to be sent on the new date. If the FUComplete field is True (the job is complete), then the code does nothing. There are two situations that this example code does not deal with (because you haven't said how or if you want to deal with them);

1) Making sure this code is not run too often against the same customer (i.e. - generating more e-mails to one customer than was intended)

2) What you want to happen after 24 days.

If Not FUComplete Then 
'project is not complete so add 4 days to Date Contacted
 
Me!FUDateContacted = DateAdd("d", 4, Me!FUDateContacted)
 
Dim appOutLook As Object
Dim MailOutLook As Object
 
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
 
With MailOutLook
.To = Me.EmailRecipient 'get the recipient from the form
.Subject = "Project ID:" & Me.ID & ", Street Ref: " & Me.ClientStreet 'get the street and ID from the form
.body = "This is a reminder that you need to make a follow up call to the address in the Subject line today."
.DeferredDeliveryTime = Me.FUDateContacted 'get the date from the form
 
.Send
End With
 
Set appOutLook = Nothing
Set MailOutLook = Nothing
 
End If

 

[ledmark]

Hello Beetle - The e-mail issues is really not an issue - I have a table set up with only the e-mail's and no e-mail ID. I put EmailRecipient as the countrol source for the field on the form and it now stores the e-mail address selected in the table.

For the adding 4 days to the FUDateContacted field - the code needs to run every four days until the sales person checks the box that the follow-up is complete - I say 24 days because I would hope they'd complete it by then - but they may not. So, it is ok for it to run against the same customer a number of times because it is telling the sales person they haven't completed the project yet.

If the code adds four days to the FUDateContacted field does replace the old date with the new date in the table? If so then I wouldn't have to worry about additional coding beyond adding 4 days.

I will work on this to see what happens and let you know.

 

[ledmark]

Help Beetle! OK - I put the code in like this:


Private Sub FUDateContacted_AfterUpdate()
If Not FUComplete Then
'project is not complete so add 4 days to Date Contacted

Me!FUDateContacted = DateAdd("d", 4, Me!FUDateContacted)

End If

Dim appOutLook As Object
Dim MailOutLook As Object

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

With MailOutLook
.To = Me.EmailRecipient 'get the recipient from the form
.Subject = "Project ID:" & Me.ID & ", Street Ref: " & Me.ClientStreet 'get the street and ID from the form
.body = "This is a reminder that you need to make a follow up call to the address in the Subject line today."
.DeferredDeliveryTime = Me.FUDateContacted 'get the date from the form

.Send
End With

Set appOutLook = Nothing
Set MailOutLook = Nothing

End Sub

But it didn't send the e-mail - I added End If because it kept going to the debugger. When I go to my e-mail and click send/receive it gives this error:


Task 'laura7347@hotmail.com' reported error (0x8004102A) : 'Error with Send/Receive. There was an error synchronizing your folder hierarchy. Error : 8de00002.'

Task 'laura7347@hotmail.com' reported error (0x8DE00002) : 'There is an error synchronizing your mail account. Please verify your account is configured correctly by first accessing your mail on the web. Error: 4102. Client. Object with Id 1418f51c-7b53-4a31-bae9-bc2c37c5a712 appears in more than one command. '

Should I take out the End If and see if it works four days from now?

 

[ledmark]

OK - I have to apologize because it is working. Something happened to my Hotmail account through my Outlook so I wasn't receiving any messages but when I tried it at work it automatically puts in the new FUDateContaced and sends the e-mail just fine. I don't think I'll run into any snags at the Tile Office but if I do then I'll ask for more help

Thank you so much for your help!!!
Laura

 

[Beetle]

Glad it's working but you need to put that End If after the code to send the e-mail, otherwise it's going to try to send the email regardless of whether the project is complete or not. Also, if you place this code in the After Update event of FUDateContacted, then the code is only going to run when a user enters a value in that field.

Final code should look like;

If Not FUComplete Then
'project is not complete so add 4 days to Date Contacted
 
Me!FUDateContacted = DateAdd("d", 4, Me!FUDateContacted)
 
Dim appOutLook As Object
Dim MailOutLook As Object
 
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
 
With MailOutLook
.To = Me.EmailRecipient 'get the recipient from the form
.Subject = "Project ID:" & Me.ID & ", Street Ref: " & Me.ClientStreet 'get the street and ID from the form
.body = "This is a reminder that you need to make a follow up call to the address in the Subject line today."
.DeferredDeliveryTime = Me.FUDateContacted 'get the date from the form
 
.Send
End With
 
Set appOutLook = Nothing
Set MailOutLook = Nothing
 
End If
 
End Sub

 

[ledmark]

I understand and will move the End If. Thank you so much for your help