Automating inputs in email from a form

[Guest]

Hello, this is my first post and I am relatively young in Access development.
I have a project that requires me to send an email after a certain time pd
has elapsed since the date a piece of data is entered (ie. new hire). I will
address that in another post. But as the email will be to a different person
each time, yet the same email extension (@company.com) is there a way to have
the email look to a field that contains the new recruits name and input that
in front of the @company.com? Furthermore, is it possible to always have
specific people in the cc field and fixed language in the body of the email?

Thank you so much. I am very happy I have found a site like this and look
forward to many informative exchanges.

 

[Guest]

Short answer - yes!

Long answer - 2 options use the SendObject Method or Automate Outlook.

The SendObject Method
SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject,
MessageText, EditMessage, TemplateFile)
Therefore you can statically create your entries for the Cc recipient(s) and
only make the To recipient variable (based on your form or a Dlookup). Look
it up in the Help for more info and a functional example.

Automating Outlook
Take a look at
http://msdn2.microsoft.com/en-us/library/aa159619(office.11).aspx

Also you might want to check out
http://www.cardaconsultants.com/en/msaccess.php?lang=en&id=0000000007#email
--Sending Emails Sample Db
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='SendEMail.mdb'
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Emails.mdb'
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='CodeSendEMailToAll.mdb'
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='CodeSendEMailToOne.mdb'

 

[Guest]

Thanks Daniel, I pulled some code from one of the sites you gave me but am
wondering how to reference someones name that is displayed in a field on the
form I will store the button? Or, it may be best since sometimes full names
are not used as the email name, if the user could get prompted to type the
name in...is this possible?

Set objOutlookRecip = .Recipients.Add(" ")
objOutlookRecip.Type = olTo

 

[John W. Vinson]

Thanks Daniel, I pulled some code from one of the sites you gave me but am
wondering how to reference someones name that is displayed in a field on the
form I will store the button? Or, it may be best since sometimes full names
are not used as the email name, if the user could get prompted to type the
name in...is this possible?

I would NOT count on the person's name being their email address: too much
chance of duplicate names (Jim Smith in Accounting, and Jim Smith the DBA), or
personal variants of email address for whatever reason. Any chance that you
could store each employee's EMail address in the personnel table and just pull
it up?

John W. Vinson [MVP]

 

[Guest]

John is very right (as if there was ever any doubt there).

You should consider setting up a table to cross-reference name with email
addresses. You can then loop through them in your code to populate the To
recipients as required. You could then use the table to populate a combo
box/list box... on your form with the choices for your user to select who
need to recieve the email. I would create a 2 columned control (1 being the
name and 2 being the email address) and hide the 2nd (make it's width = 0)
since it is of no real use to your users. Then simply loop through the
controls selection extracting the 2nd columns values instead of the 1st. I
hope that made sense!

If you neeed more help explain further and I can try and build a sample
solution to suit your needs but you need to give more details (form name,
controls being used with their names,explain the process in greater detail)
--
Hope this helps,

Daniel P

 

[Guest]

I grasp what you are saying but building it is another thing. Let me start
by saying I am relatively new working with VBA in excel and access and want
to ask if there is a good resource/book to get a really good understanding
of the fundamentals. I often find myself in situations where something is not
as fully explained as I would like and it results in error in the code that I
don't understand.

Daniel, to your point, here is the task I have been given. when a new
recruit is hired at our firm, we enter them into a process that unfolds over
the course of their first year. This process has development steps
throughout it to facilitate their transition to our business taking place on
their 1 month, 2 month, 6 month, and year anniversary. Being that the
development steps are different and so are the people that will be
coordinating them, I would like to be able to automate the process of
sending out emails to those people that are involved in the step taking place
at that time. THis would mean that I would need a function to look at the
current date and subtract that from the day the recruit was hired to get a
day count. Then once a specific day count is reached (say 30) this triggers
a stored email, with static body, subject, and CC, yet a different To
field...the recruit email. This is how the process would unfold throughout
the year...day 60 hits and bam same thing. Is this possible and probable for
a beginner to accomplish? Please let me know if any of this is unclear.
Thank you already for your quick replies and insights.