Outlook automation with info from Access forms

[Jason Foley]

I have setup the automation piece in Access XP to use the outlook email
client. However how do I have Access XP insert information from a form into
the email that automation creates?

 

[Rick]

Jason,

Have you found your answer yet?
I have been searching for the same information: to transfer information from
Access --> Outlook -->Internet --> Outlook --> Access and back again.

Any ideas or assistance would be appreciated.

 

[TRM]

I don't normally offer suggestions, as I am still learning - but, I have used
the code below to set up appointments and send emails from an Access
database. Maybe it will help?! I've cut some code out to shorten it up a
little, but I think the important stuff is in there still!

Good luck!

Function SetAppt(dteDeadline As Date, strType As String, strDoc As String)
On Error GoTo Err_Edit:

Dim strMessage As String
Dim objOutlkApp As Outlook.Application
Dim objAppt As Object

strMessage = "deadline is today. Please verify appropriate action has been
taken!"
Set objOutlkApp = CreateObject("Outlook.Application")
Set objAppt = objOutlkApp.CreateItem(olAppointmentItem)

With objAppt
.Subject = strType & " Deadline(s) for " & strDoc
.Body = strType & " Deadline set, please verify action has been taken
for: " & strDoc
.Start = dteDeadline & " 10:00"
.End = dteDeadline & " 10:30"
.ReminderSet = True
.Save
End With

Err_Edit:
MsgBox Err.Description
Resume exit1:

exit1:
End Function


Function SendApp(strPath As String, strDoc As String, strAuthor As String,
strSuper As String, strDocType As String)

Dim objOutlook As Outlook.Application
Dim objMessage As MailItem
Dim strNote As String
Dim strSuperAddr As String
Dim strBlindCopy As String

strSuperAddr = DLookup("[EmailAddress]", "Employee", "[Employee]= """ &
strSuper & """")

Set objOutlook = Outlook.Application
Set objMessage = objOutlook.CreateItem(olMailItem)

strNote = "I have just approved this SOP."
strDoc = strDoc & ".doc"

setfullpath1:
strPath = strPath & strDoc
With objMessage
.To = strSuperAddr
.CC = strAuthorAddr
.BCC = strBlindCopy3
.Subject = "Approval of " & strDoc
.Attachments.Add strPath
.Body = strNote
.Send
End With

Set objOutlook = Nothing
Set objMessage = Nothing

End Function

 

[Rick]

Thanks for the note. However, I had something a bit more ambitious in mind.

I am an Access developer, but I don't know how to program Outlook through
Office Automation.

I know this is a lot to do, but I think that the following could be
accomplished through Office Automation completely through vba code running in
Access:

1.) create a part price inquiry form in Outlook
2.) create an inbox folder in Outlook (called "price quotes")
3.) create a rule in Outlook called Price Quote so that all incoming mail
from a specified email address is moved to the price quotes folder
4.) create a data collection form in Access (done)
5.) save the price inquiry data in an Access table (done)
6.) transfer the data from the Access form to the Outlook form
7.) send the Outlook form to a specified recipient
8.) the recipient enters the price, availability, shipping information and
comments, etc information into the Outlook form and sends it back as a reply.
9.) Access checks the price quotes folder in Outllok every 20 minutes (or
set time interval)
10.) if there is a message in the folder, then copy the reply information
back to the matching price inquiry record in the Access table
11.) pop up a message to the Access user that an answer the the inquiry has
been received.

I'm sure that somewhere out there is a Microsoft MVP who knows how to do
this with Automation.

Thanks

Rick
Harvest Data Systems