467,888 Members | 1,383 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,888 developers. It's quick & easy.

Open outlook in access with body auto fill from form

Hey I am so close from finishing this database I just need one more issue solve thanks. Basically I have a code within a form that opens up outlook and fill in the body of the email, among other informations, with event infos, but I need it to also fill in the Venue. The problems is the venue exist in the form as a subform, and I don't know how to reference it in the code. Here is the code I hope you can help, I ran into so much problems trying to finish this database, mostly technical, and my teacher never used outlook object so he couldn't help. But at this point I just want to get this done. Thanks for your help, I basically want the reference at the .Body area.

Private Sub Command31_Click()
On Error GoTo Err_SendEmail

Dim objOutlook As New Outlook.Application
Dim objMail As Outlook.MailItem
Dim sSQL As String, db As DAO.Database, rs As DAO.Recordset
Dim sTitle As String, sFile As String, sErr As String

'Prelims
DoCmd.SetWarnings False
DoCmd.Hourglass True
Set db = CurrentDb

'Prepare email message
Set objMail = objOutlook.CreateItem(olMailItem)
With objMail

'Build recordset on recipients
sSQL = "SELECT [EMAIL_ADDRESS] FROM MEMBER_CONTACT;"
Set rs = db.OpenRecordset(sSQL)

'Add Recipient
While Not rs.EOF

With .recipients.Add(rs![EMAIL_ADDRESS])
.Type = olTo
End With

rs.MoveNext
Wend
rs.Close
Set rs = Nothing

'Add the subject
.Subject = "Multi-Asian Club Event: " & Me![EVENT_NAME]

'Add standard message text to body
.Body = .Body & "Event Name: " & Me![EVENT_NAME] & vbCrLf
.Body = "Description: " & Me![EVENT_DESCRIPTION] & vbCrLf
.Body = .Body & "Event Date: " & Me![EVENT_DATE] & vbCrLf
.Body = .Body & "Start Time: " & Me![START_TIME] & vbCrLf
.Body = .Body & "End Time: " & Me![END_TIME] & vbCrLf

'Closure
.Body = .Body & vbCrLf & "Regards" & vbCrLf & vbCrLf & "Your Club"

.Display

End With

'Send the mail message
Set objMail = Nothing
Set objOutlook = Nothing

Exit_SendEmail:
DoCmd.SetWarnings True
DoCmd.Hourglass False
Exit Sub

Err_SendEmail:
sErr = "Error " & Error & " / " & Err
MsgBox sErr, vbInformation + vbOKOnly, "Error on Email subroutine"
Resume Exit_SendEmail


End Sub
Mar 30 '08 #1
  • viewed: 3290
Share:
6 Replies
Expert Mod 2GB
Hi. You should find the answer you need in the following HowTo article linked here: Referring to Items on a Subform. I would show you the exact syntax, but you haven't told us the name of the subform or the name of the field storing the venue. As long as the subform is on the same form as the code you are using it will be along the lines of

Expand|Select|Wrap|Line Numbers
  1. <someexpression> = Me.[subform name].Form![venue control name]
-Stewart
Mar 30 '08 #2
Hey Thankyou very much, I have been pulling my hair out because of this. by the way the SUBFRM_VENUE and the field name is VENUE_ADDRESS. I did manage to get the venue to show up in the email, but it only show the first venue, so if an event has more then one venue only the first one shown up.
Mar 30 '08 #3
Expert Mod 2GB
Hi. Glad you got this working. When you refer to a subform control value you are referring to the currently-active record, not all records, hence the transfer of just one venue address. If you want to transfer all venue addresses you really need to take a different approach. You could use a loop in code to process a copy of the subform's recordset, which is normally already filtered by the parent-child field link to the main form.

A skeleton for the code for this is shown below:

Expand|Select|Wrap|Line Numbers
  1. Dim rstVenues as DAO.recordset
  2. Dim VenuAddresses as String
  3. Dim Addresscount as Integer
  4.  
  5. Set rstVenues as Me![SUBFRM_VENUE].Form.Recordsetclone
  6. Do while not rstVenues.EOF
  7. Addresscount = Addresscount + 1
  8. If Addresscount = 1 then
  9. VenuAddresses = rstVenues![Venu_Address]
  10. Else
  11. VenuAddresses = VenuAddresses & "; " & rstVenues![Venu_Address]
  12. End If
  13. rstVenues.Movenext
  14. Loop
  15. rstVenues.Close
You may need to add a reference to the MS DAO 3.x object library (if compiler does not recognise the DAO.Recordset statement) - from VB editor choose Tools, References and tick the highest-numbered MS DAO object library.

-Stewart
Mar 30 '08 #4
Thanks for the reply, but I am getting a compile error "type mismatch" that highlights the .RecordSource, any idea, thanks
Mar 30 '08 #5
Hey just want to thank you Stewart, I thought it through and it seems that the club will rarely have an event with more then one venue, so I decided not to add complication. Our teacher said in the beginning that we should stay away from project that requires emailing, and that we were not expected to use any VBA, but I decided to just learn VBA and use it. I think it has been successful and a bit fun, and people like you has helped me a lot so thanks.
Mar 31 '08 #6
Expert Mod 2GB
Hey just want to thank you Stewart, I thought it through and it seems that the club will rarely have an event with more then one venue, so I decided not to add complication. Our teacher said in the beginning that we should stay away from project that requires emailing, and that we were not expected to use any VBA, but I decided to just learn VBA and use it. I think it has been successful and a bit fun, and people like you has helped me a lot so thanks.
Sorry Leo - I put Recordsource instead of Recordsetclone - can't imagine what I was thinking at the time. Anyway, I think it is wise of you to avoid unnecessary complications in the circumstances. Well done for taking the simpler approach.

Good luck with future projects.

Regards

Stewart
Mar 31 '08 #7

Post your reply

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

Similar topics

2 posts views Thread by MaxH | last post: by
reply views Thread by TheCoder | last post: by
2 posts views Thread by Jesper | last post: by
2 posts views Thread by Raymond Du | last post: by
3 posts views Thread by Jeff | last post: by
23 posts views Thread by andyoye | last post: by
1 post views Thread by andyoye | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.