programming ms outlook and ms access, very complicated problem 2

[hengsin]

Anyone here doing similar thing before? I want to know it's possible to do the following task in Microsofoft Access and
Microsoft Outlook.

My boss wanted to me develop a Action Tracking Database system. There is one major form, frmMeeting which is to use to key the info such as MeetingID, MeetingDate, MeetingDescription etc. There is also another subform, subformAction which is used to key in the action item arises during a meeting. The fields in the subformAction are ActionID, ActionDescription, ActionItemOwnerEmailAddress etc.

There is one button in this form. Once the user clicks on this button, an email with all the attendees' email address will fill in the "To" field. A report about current meeting will be attached to this email message as Snapshot format.

MeetingDescription will become subject of the email. There will be a standard email message on the body. It can be easily done using SendObject Method and DAO recordset method in Ms Access. I already got it done. Since this code is attached on a button, it's call Private Sub btnSendEmailWithReportAttach_Click(). Let call this as first part.

There will be another button on this form. Once the user clicks on this button, each action item will be turn into a Task in Microsoft Outlook with each Action Item Owner's email address fill in "To" field on the Assign TAsk and the TAsk will be automatically send to each Action Item Owner. I got it done using DAO recordset and Automation Microsoft Outlook method.

TO clarify it, let assume for a particular meeting with 10 action items. Every Action Item will has one Action Owner. once the user clicks on this button, Microsoft Outlook will automatically fill in with 10 differents Task Item. Each Action Owner Email address will fill in the "To" field of each corresponded TAsk Item. After that, the message with send out automatically. Since this code also attached to a button, it's call Private Sub btnAddTaskAutomaticallyAndSend_Click(). Let's call it here as second part.

Now. i want to let user to click on only one button and get everything done. So, i put the following code somewhere in the btnAddTaskAutomaticallyAndSend_Click():

Call btnSendEmailWIthReportAttach_Click

Everything is done until this moment for what i want.

Now what i want to do is attached all the Task Item as attachement on the email generated using SendObject method as mention on the first part. If i do it manually, when the email generated with Current Meeting Report attached as attachment, i need to go to Insert->Item->Task and select the Task what i want to add as attachment. I'm aware of every single Task Item must be saved on the computer before i can actually add in manually.

Can it be done automatically from MS Access with a click of a button. Once something is saved on Microsoft Outlook because i don't know how to track it from Microsoft Access. In Ms Access, at least i can track something because there is Primary key.

 

[sha76]

You'll need to use MAPI objects, once you've referenced the Outlook library view it through object browser & use F1 for help (for some reason it doesn't appear in the help index!). I've done some work with contacts & e-mail this way, but not tasks, so, sorry, I can't give you any examples.

Hope this helps

Sharon

 

[celtc]

Part 1 is code I use.
Part 2 is some pertinent help file
Part 3 an example from help file as to how to change the code shown to do what you want

1.
Public Sub SendEmail(tostr As String, locstr As String, substr As String, bodystr As String)
'needs reference to Microsoft Excel 9.0 Object library
Dim app As Outlook.Application
Dim mitem As MailItem
Dim att As Attachments
Set app = CreateObject("Outlook.Application&quot
Set mitem = app.createitem(olmailitem)
Set att = mitem.Attachments
att.Add locstr
mitem.To = tostr
mitem.Subject = substr '& " (Test Only so Delete)"
mitem.Body = bodystr '& vbCrLf & "This is Test only so Delete this"
mitem.Display
End Sub

2.
Add Method (Attachments Collection)
Creates a new attachment in the Attachments collection, and returns the new attachment as an Attachment object.

Syntax

objAttachments.Add(Source, [Type], [Position], [DisplayName])

objAttachments Required. An expression that returns an Attachments collection object.

Source Required Variant. The file (represented by the full path and file name) or item that constitutes the attachment.

Type Optional Long. The type of attachment. Can be one of the following OlAttachmentType constants: olByReference(4), olByValue(1), or olEmbeddedItem(5).

Position Optional Long. The position of the attachment within the body text of the message.

DisplayName Optional String. The display name of the attachment. Ignored unless Type is set to olByValue.

Remarks

The following table describes the purpose of each OlAttachmentType constant value.

Constant Use to
olByReference Create a shortcut to an external file
olByValue Embed attachment in the item
olEmbeddedItem Create a shortcut to an Outlook item


When an Attachment is added to the Attachments collection of an item, the Type property of the Attachment will always return olOLE(6) until the item is saved.

To ensure consistent results, always save an item before adding or removing objects in the Attachments collection of the item.
3.

This Visual Basic for Applications example creates a new mail message and attaches the first contact in the default Contacts folder.

Set myOlApp = CreateObject("Outlook.Application&quot
Set myNameSpace = myOlApp.GetNameSpace("MAPI&quot
Set myFolder = _
myNameSpace.GetDefaultFolder(olFolderContacts)
Set myFirstContact = myFolder.Items(1)
Set myItem = myOlApp.CreateItem(olMailItem)
Set myAttachments = myItem.Attachments
myAttachments.Add myFirstContact

 

[mp9]

See also

http://www22.brinkster.com/accessory/modules/005.shtml

- provides a feature-rich, Outlook-based solution to sending an e-mail from within Access that also supports things like voting buttons, message priority, the "From" mail field, etc.