Email with Access

[EllieFant]

I have learned so much about access lately that after reviewing a database I made a year ago, I decided it needed a facelift. Well, silly me, I asked the people I work with - any wishes for the new version of the database? Well I got an answer, and I don't know if it is possible.

The database tracks Safety Issues until they are fixed and are no longer issues. Some of these issues come from "Employee Concerns". What they want the database to do is the following.

When entering a date in the DateComplete field, they want the database to determine if this is an "employee concern" by looking in the InspectionType Field. If it is, then they would like a prompt to ask them if they want to send an email to the employee explaining the resolution. If they do, then they want a new email message to open up (We use Outlook as our email client) and then put any information that would be entered into the resolution field into the body of the email.

Is this possible? I told them I didn't think so, but that I would ask the one group of people that could supply me with an answer if there was one.

We use Access 97 right now, but will be switching to 2000 soon (This is another reason for the update in the database).

Thanks so much Ellie
**Using Access 97**

lena.wood@wgint.com (work)
elliefant@starband.net (home)

 

[MARKNLA2]

TRY ACTIVATING A MACRO ON THE FORM'S CONTROL. THE MACRO WOULD INCLUDE THE SENDOBJECT ACTION.

 

[EllieFant]

I was hoping for a bit more help than to use a macro. Thanks anyway.
Ellie
**Using Access 97**

lena.wood@wgint.com (work)
elliefant@starband.net (home)

 

[demoman]

Ellie

I do this all the time in VB. I am pretty sure it can be done in Access '97, but I cannot say for sure until tomorrow (do not have '97 on my home machine). You can check Tools>references to see if the Outlook object library is there (I know it is in 2000).

Anyway, if someone does not beat me to it, I will send you some code sample tomorrow.

 

[RSGB]

Hi

I use this in code:

DoCmd.SendObject acSendNoObject, , acFormatRTF, "Person 1", "Person 2", , "Title here e.g. ref" & YourReferenceValue, "Put the text of the message here."

Need to have a reference to Outlook selected from Tools/References to use this.

Hope this helps

RSGB

 

[lxn]

Oh yeah, this is definitely doable, well at least the Emailing part, I do Outlook automation all the time. You could use DoCmd.SendObject like RSGB suggested, or you could use CreateObject("Outlook.Application&quot to do this. Personally I found the latter more clear and less problematic. Here's some code you might be able to use:

If (Not lstrTo = vbNullString) Then
'<start outlook application>
Set lobjOutlook = CreateObject(&quot;Outlook.application&quot
'<create new mail item (0)>
Set lobjMail = lobjOutlook.createitem(0)
Let lobjMail.subject = lstrSubject
Let lobjMail.Body = lstrBody '<add body>
'lobjMail.HTMLBody = lstrHTMLBody '<use HTML body>
Let lobjMail.to = lstrTo '<apply To list>

'Let lobjMail.SentOnBehalfOfName = lstrFrom
'Let objMail.cc = lstrCc '<apply CC list>
'Let lobjMail.bcc = lstrBCC '<apply bcc list>

'<add attachments>
Set lobjAttachment = lobjMail.attachments
lobjAttachment.Add path1, , , <Description of the file>

'lobjMail.display
lobjMail.send
lobjOutlook.Quit

End If

The lines commented out are optional, I don't think there's any need to explain them. This would allow you to send emails automatically. All you need to do is to modify the code and work on the conditions before sending emails. Hope this helps a bit.

 

[EllieFant]

Thank you for your help everyone. I am still a bit confused as doing much more than the basics is like a foriegn language, but at least I have some sort of direction and know that what I want to do is possible. I just won't tell those I work with it can be done until I make it work.

I am going to be taking a few classes regarding Access 2000 soon (in the next few weeks) and hopefully those will help me understand more of what you are trying to say. I am going to print this out and take it to the class with me so that when something is explained I can take notes.

You guys are awesome!

Ellie
**Using Access 97**