By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,916 Members | 1,078 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,916 IT Pros & Developers. It's quick & easy.

Adding multiple email appointment attachments to a single email from ms access

100+
P: 153
Alright so I've got this Outlook code written in VBA in Access. The first part, which works, records information about appointment times based on the required days before notification of certain contracts and then it adds them to the outlook calendar of the current user. This code works and is nested within a bunch of if statements because it only needs to trap certain appointments. The table I create with this code is later used to attempt to create an email that sends out multiple appointments by means of adding an attachment, this is where I am having trouble. First I will post the code that works and then the code that does not work.

This is the code that works:

[PHP] stSQL = "SELECT * FROM AddedToOutlook WHERE DatabaseReferenceNumber = " _
& recSet1("DatabaseReferenceNumber2")
' (This beginning part is just checking another table to see if these appointments have already been added to Outlook.)

recSet6.Open stSQL, con6, adOpenKeyset, adLockOptimistic
If recSet6.EOF Then
recSet6.AddNew

Dim objOutlookRecip As Outlook.Recipient
Dim outObj As Outlook.Application
'Dim outItem As Outlook.ContactItem
Dim outAppt As Outlook.AppointmentItem
Dim myattachments As Outlook.Attachment
'Set outItem = outObj.CreateItem(olContactItem)
Set outObj = CreateObject("outlook.application")
Set outAppt = outObj.CreateItem(olAppointmentItem)
With recSet8
.AddNew
.Fields("Start") = recSet1.Fields("NotificationDate2") _
& " " & recSet1.Fields("ApptTime2")
.Fields("Subject") = "Contract Notification/End" & " " _
& recSet1.Fields("DatabaseReferenceNumber2") _
& " " & recSet1.Fields("Vendor2")
.Fields("Body") = "Contract Notification/End" & " " _
& recSet1.Fields("DatabaseReferenceNumber2") _
& " " & recSet1.Fields("Vendor2")
.Fields("ReminderMinutesBeforeStart") = recSet1.Fields("ReminderMinutes2")
.Update
End With
With outAppt
.Start = recSet1.Fields("NotificationDate2") _
& " " & recSet1.Fields("ApptTime2")
.Duration = .AllDayEvent
.Subject = "Contract Notification/End" & " " _
& recSet1.Fields("DatabaseReferenceNumber2") _
& " " & recSet1.Fields("Vendor2")
.Body = "Contract Notification/End" & " " _
& recSet1.Fields("DatabaseReferenceNumber2") _
& " " & recSet1.Fields("Vendor2")
.ReminderMinutesBeforeStart = recSet1.Fields("ReminderMinutes2")
.ReminderSet = True
.RequiredAttendees = "JPollard@phcs.com"
.Save
End With

'DoCmd.RunCommand acCmdSaveRecord
recSet6.Fields("AddedToOutlook") = True
recSet6.Fields("DatabaseReferenceNumber") = recSet1.Fields("DatabaseReferenceNumber2")
recSet6.Update
End If[/PHP]

Which is later followed by this code which does not seem to work:

[PHP]Dim outMail As Outlook.MailItem
Set outMail = outObj.CreateItem(olMailItem)
Dim outAppt2 As Outlook.AppointmentItem
Set myattachments = outMail.Attachment
' Fill out & send message...
outMail.To = recSet8.Fields("RequiredAttendees")
outMail.Subject = "Contract Notification/End..."
recSet8.MoveFirst
Do Until recSet8.EOF
outAppt.Start = recSet8.Fields("Start")
outAppt.Duration = 15
outAppt.Subject = recSet8.Fields("Subject")
outAppt.Body = recSet8.Fields("Body")
outAppt.ReminderMinutesBeforeStart = recSet8.Fields("ReminderMinutesBeforeStart")
outAppt.ReminderSet = True
outAppt.RequiredAttendees = recSet8.Fields("RequiredAttendees")
Set outAppt2 = outAppt
' (tried putting separate code in a module but didn't work either) DoCmd.OpenModule "modAppointment"
Set myattachments = myattachments.Add(outAppt)
recSet8.MoveNext
Loop
outMail.Body = _
"HI"
outMail.Send


'For Each objOutlookRecip In .Recipients
'objOutlookRecip.Resolve
'Next
' Clean up...

Set outMail = Nothing
'Set outItem = Nothing
Set outObj = Nothing
Set outAppt = Nothing[/PHP]

It seems that it is possible because an earlier version of this code was able to add five appointments but only added the last appointment in the recordset which I checked and surely it had 5 records in the table (but this number will vary based on the number of contracts that require notification within a certain number of days) but if I have 5 records...and I say move first, it seems like the only thing I'm acheiving by running this .EOF loop is counting from 1 to 5 and then multiplying the last entry by it. So that didn't make much sense.

The older version of the code can be found here:
http://www.thescripts.com/forum/thread583157.html

The code for the larger part of this program which would help you understand the context of this function can be found here:
http://www.thescripts.com/forum/thread581521.html
although I don't think this is necessary to figure out the problem...but people always like to have more code :)

Any help with this would be greatly appreciated as I have been struggling with this for a while and Microsoft does not seem to offer any help with this...nor does anyone else for that matter.

Cheers,
Kosmös
Jan 5 '07 #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,492
Kosmos,
I suspect that you will need to do some work on this before you can expect any help. As a Senior Member who has started to respond to questions yourself, you will be starting to understand the importance of how a question is asked. With such a complicated question you will need to do some work yourself finding out the problem - asking someone else to find a problem in a bunch of code that size is not reasonable - especially as you should have some idea of the problems you experience (where does it fail? What error message, if any, do you get?).
I can't tell you to redo this, but I can suggest that, if you don't, you're less likely to receive a helpful reply.
Don't forget - more is not always better.
Good luck.
-Adrian.
Jan 6 '07 #2

100+
P: 153
I really didn't think it was that much code...and besides the problem really lies in the statement where I add the appointment to myattachments (but I guess I should have clarified that a little bit better). I've done a ton of research and can't find the answer to this question. The reason I tend to post more code is that whenever I have posted a shorter question, people have tended to ask me for the full code so they could see the problem in context. Anyways I've got a busy day ahead of me, but I do apologize for posting too much code. I didn't mean for people to debug my entire code and that was entirely my fault...but it does at least give people tons of example code from which they can adapt their own code if they're searching for a similar problem.
Jan 8 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.