Open outlook in access with body auto fill from form

[LeoK]

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

[Stewart Ross]

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

<someexpression> = Me.[subform name].Form![venue control name]

-Stewart

[LeoK]

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.

[Stewart Ross]

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:

Dim rstVenues as DAO.recordset
Dim VenuAddresses as String
Dim Addresscount as Integer
 
Set rstVenues as Me![SUBFRM_VENUE].Form.Recordsetclone
Do while not rstVenues.EOF
Addresscount = Addresscount + 1
If Addresscount = 1 then
VenuAddresses = rstVenues![Venu_Address]
Else
VenuAddresses = VenuAddresses & "; " & rstVenues![Venu_Address]
End If
rstVenues.Movenext
Loop
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

[LeoK]

Thanks for the reply, but I am getting a compile error "type mismatch" that highlights the .RecordSource, any idea, thanks

[LeoK]

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.

[Stewart Ross]

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