Emailers and Add-ons

[Freehal04]

Hi all. I was wondering if someone could shed some light on emailing and
add-ons associated with that. I know there is code you can write that will
send out an email if you put it in On-whatever command, ie On-click,
on-doubleclick, etc.

But what I was wondering is, if I create a field that recognizes a 10day
prior window to a due date, can I create an automatic email and have it sent
out automatically without having my computer on all the time?

MY DB will be on the server so wouldn't that mean my cpu is out of the
equation. And I've heard that there are third party programs that can do all
this. Any experience in this or advice.

 

[Wayne-I-M]

Hi

You don't really need to add lots of code to send e mails to anyone in your
DB provideing you set it up correctly.

In you table you should have a field formated as Hyperlink.
You place the email address i this field
BUT
The hyperlink is a "MailTo" NOT a html
So insert e mail address like this
Mailto:[email protected]
Mailto:[email protected]
etc
etc.
If you have a control on a form with this field as it's source - just click
it to send the e mail.

Or

Put a button on the form and use something like

DoCmd.SendObject acReport, "ReportName", "RichTextFormat(*.rtf)",
Forms!FormName!ControlName, Forms!FormName!ControlNameCC, "", "EMail Header
entered here", "EMail text enter here", False, ""

Notes
Forms!FormName!ControlNameCC
We send a copy of all reports to our accounts office. Delete this section
if you don't need a CC

EMail Header entered here
This is a short string that is put into the Subject line of the e mail (We
use "weekly report of ........)

EMail text enter here
This is a short string that is put into the Details section of the e mail
(we use "If there are any problems with this mail please contact .....)

False
This section of the code is use to say if the e mail is opened in outlook
before it sends (to edit). We use False as they are just attached reports.
Put True if you want to edit the mail prior to sending.


To automate sending the e mail

You could set up windows sceduler to run a macro as at time
So you could start access, open a form, send an email, etc.
Or
Assumeing your database in turned on then try this
We use it all the time at work to send weekly report to clients at 1 minute
past 10am Monday - Oh - there are other methods of automating e mails this is
just one of them (we use it as the admin manager likes this one)

Create a marco with a condidtion something like If the time is XXX and if
today is a monday etc etc
You don't really need the above condition but it helps in case someone
forgets to send the mails

The macro opens a form.
On the form is a list of e mails that we send reports to.
OnOpen of the form the code is run.
Dead simple

Oh I have used a macro instead of VBA to open the form so the admin manager
can change the time of the report sending without going into the VBA (which I
don't want them messing about with)

Just a point if you are using Outlook then you will have a security warnign
saying a programme is trying to send an e mail - you "can" get round this by
send the mail direct to the mail server (don't use outlook) BUT BUT BUT I
would suggest you don't. Just pressing the enter key a few times is MUCH
better than the chance of losing clients after send a virus. So live with
the warning - it's one of mircosoft's better ideas.

Good luck

 

[Freehal04]

Wayne,

Thanks for that input. Is there a way that a macro can be written to
function when a particular field in my table calculates a certain date? It
sounds like from what you said it's possible but I'm new to writting my own
expressions, macros, and VBA.

I'm not sure where that command line..."DoCnmd.SendObject...." is supposed
to go. If you could point me in the right direction it would be appreciated.

Freehal04

 

[Wayne-I-M]

Hi

I will make some assumptions that may or may not be correct. 1st I assume
you have a field in your table called DueDate and that this field is a
date/time field and it contains a date by which something is due.

I assume you have a form with “stuff†on. (if not then you should create a
form – use the wizard to do this for you). Make sure that one of your
controls on your form is sourced (based on) on the field DueDate.

Next I assume you have a report that you want to send to someone and that
you have the e mail address of this person in your table and that this field
is a hyperlink (mailto not http).

Still OK?.

You need to (I think) have a number of methods of sending this report by e
mail to the person – on pressing a button on your form and automatically 10
days prior to the date in DueDate.

1st the button press
Create a button on your form.
To do this open the form in design view
Click View
Click Toolbox
Select Command Button
Click the form where you want your button (don’t you can position it exactly
later)
The Button wizard will open
Click Cancel (you will write your own code for it)
Right click the button and select properties and the properties box will open
In the Other column you should put ButtonMail in the Name row
In the Event Column select (click) the OnClick row
Click build (…) and select code
You will see this

Private Sub ButtonMail_Click()

End Sub

You are going to place the new code between these 2 lines.
Cut and paste this code between the lines.
DoCmd.SendObject acReport, "111", "RichTextFormat(*.rtf)", Forms!222!333,
Forms!222!444, "", "ABC", "DEF ", False, ""

It should now look like this

Private Sub ButtonMail_Click()
DoCmd.SendObject acReport, "111", "RichTextFormat(*.rtf)", Forms!222!333,
Forms!222!444, "", "ABC", "DEF ", False, ""
End Sub

We need to change the code so that it works for you.
You “must†leave everything else in place ( all ,,, and “â€â€ and !!!)
You need to type “exactly†the name of the report where I have put 111
(delete the 111)
You need to type “exactly†the name of the Form where I have put 222
You need to type “exactly†the name of the Control that contains the email
address where I have put 333
You need to type “exactly†the name of the Control that contains the Copy to
email address where I have put 444 – if you don’t have one simply delete the
line of code so it looks like this at the moment (by now it should have the
form name and e mail control name)
Forms!222!333, , "", "ABC", "DEF ", False, ""
Type the e mail Subject line where I have put ABC
Type the e mail detail where I have put DEF

Save the form a press the button – it should now send the e mail.

Next - to automate the report 10 days prior to DueDate

I have assumed that you have the DueDate field/Control on your form
somewhere and the e mail address field/control

Open your form in design view

Right click somewhere outside of the detail (the grey section) and open
properties box.
In the event column select OnOpen (this will run the code when you open the
form)
You will see 2 lines of code

Private Sub Form_Open(Cancel As Integer)

End Sub

and you are going to cut and paste this code in between so it should looks
like this


Private Sub Form_Open(Cancel As Integer)

If Date = DateAdd("d", -10, Me.DueDate) Then
MsgBox "The reminder mail will now be sent", vbOKOnly, "E Mail Due"

DoCmd.SendObject acReport, "111", "RichTextFormat(*.rtf)", Forms!222!333,
Forms!222!444, "", "ABC", "DEF ", False, ""

End If
End Sub

You need to change the codes 111 222 333 444 as above.

There are a few improvements that I have not mentioned that you could leave
till later like creating a table to save the time and date that the report
was sent and use this to activate the button – so if the report has already
been sent then you will not send it twice. You could have an option to
cancel when the message appears, you could search this forum on a special
type of macro called an AutoExec that would run this reports without opening
the form, etc, etc but for now I hope you the basics of what you want.

 

[Freehal04]

Wayne,

Simply awesome. That is almost exactly what I needed. Thanks so much.

Freehal04

 

[Freehal04]

Wayne, I need some clarification. When you refer to '333' and '444' in the
code you wrote, what do you mean by 'control'?

 

[Wayne-I-M]

I may be wrong on this (english is not my language) but to me a control is
any thing on a form that does "something" or stores "something" (so not a
lable String/Line, etc). So this could be a combo, text box, list, calendar,
etc, etc. No doubt someone will let you know if I am wrong (I don't mind as
its the way I learn more)

So 222 333 444 etc are just to show you where to put the name of the control.
NOTE - IMPORTANT.
Th name of the control may be different to the name of the field it is based
on.
You can find the name of the control by opening the properties box and
looking in the Other column - name row.

Cut and past this code

Private Sub ButtonMail_Click()
'change ButtonMail to the real name of the button'
DoCmd.SendObject acReport, _
'Change 111 to the name of the report'
' "111", "RichTextFormat(*.rtf)", _
'change 222 to the name of the form'
'change 333 to the name of the control with the email address'
Forms!222!333,
'change 444 to the name of the CC email address if you have one'
Forms!222!444, _
'change ABC to the subject line of the code'
"", "ABC", _
'change DEF to the details section of the e mail'
"DEF ", _
False, ""
End Sub


This is a sample of code taken from one of my databases - just so you can
see what it looks like

Note this is all on one line unless you use _ (thats a underscore after a
space) as above.

DoCmd.SendObject acReport, "rptAuditOnLine", "RichTextFormat(*.rtf)",
Forms!frmAuditSelector!txtEMail, Forms!frmAuditSelector!txtEMailCC, "",
"Audit report", "Banking - income report" attached, False, ""

In this case I have controls called
txtEMail
txtEMailCC
The title of the report is rptAuditOnLine
The title / subject of the email is Audit report
The details section of the email contains this Banking - income report"
attached
It is not open for editing (so it say False at the end of the code)

Hope this helps