Use data in access form to create email

[BSJoJaMAx4]

Hi

Just wondering is it possible to export data from access to a outlook email using a predefined template? i have a database which records meeting room bookings. Currently the data has to be manually entered into access, then manually entered into a confirmation email. Im hoping by pressing a button then data can pre populate so it can be sent quicker.

Thanks

[June7]

Bing: access vba outlook template

See what pops up. https://social.msdn.microsoft.com/Fo...rum=outlookdev

You may find it more trouble than it's worth, if even possible - I've never tried.

How much 'boiler plate' text do you need in message? Message body can be constructed with literal text strings and dynamic variables for data and transmitted with SendObject or Outlook automation.

[BSJoJaMAx4]

Hi June

Thanks for that. What I am using it for is to book conference rooms. I capture data such as name of person, date of meeting, start and finish times, etc. What I am hoping is on the data entry screen to have a button which will place the data into an email (I have bolded what I need to use from the data). eg

Hi Fred


Your booking for meeting room number XX is confirmed as detailed below:
Date: 1/9/2015
Time: 1100 - 1300
Title: Meeting for dummies
Setup required: Theatre
Number attending: 50

Please note the following important information:

and so on

If it cant be done on the record capture screen, happy to run it somewhere else.

[June7]

Simplest is SendObject method and Outlook.

Code behind button would concatenate literal text and data from the form.

strMsg = "Hi, " & Me.persontextbox & vbCrLf & vbCrLf & "Your booking for meeting room number " & Me.roomtextbox & " is confirmed as detailed below: " & vbCrLf & "Date: " & Me.datetextbox & vbCrLf & "Time: " & Me.timetextbox & vbCrLf & "Title: " & Me.titletextbox & vbCrLf & "Setup required: " & Me.setuptextbox & vbCrLf & "Number attending: " & Me.attendtextbox
DoCmd.SendObject , , , Me.emailtextbox, , , "Meeting Confirmation", strMsg

[BSJoJaMAx4]

Sorry but can you step me through how to add it?

[June7]

I don't use macros, only VBA. There is macro equivalent of SendObject and suppose it could do the concatenation I suggested.

What exactly about the suggestion do you not understand? Do you know how to create a button on form? Do you understand concatenation?

[BSJoJaMAx4]

I just dont understand how to implement the suggestion. Do I implement on the capture form or from a menu? I think this is saying to run from the form so I have added a button but then I get a command button wizard with different categories and actions. Is this the right way?

[June7]

I don't use the wizards. And no, the wizard won't generate the code needed. So just cancel the wizard and let button get created them manually edit its properties.

And yes, button on the data entry form.

In the button Click event property, select [Event Procedure], click the ellipsis (...) to open VBA editor at the procedure. Type code.

Or build [Embedded Macro].

[BSJoJaMAx4]

I am getting a compile error method or data member not found. Its stuck on me.[start time] (works through the rest of them OK to that point).

[June7]

Post your code for analysis.

[BSJoJaMAx4]

Error is Compile error: Method or data member not found

And errors at Me.[Start Time]

Code:

Private Sub Command132_Click()
strMsg = "Hi, " & Me.[Booking Officer] & vbCrLf & vbCrLf & "Your booking for meeting room number " & Me.Rooms & " is confirmed as detailed below: " & vbCrLf & "Date: " & Me.[Booking Date] & vbCrLf & "Time: " & Me.[Start Time] & vbCrLf & "Title: " & Me.titletextbox & vbCrLf & "Setup required: " & Me.[Set Up] & vbCrLf & "Number attending: " & Me.[Number of People]
DoCmd.SendObject , , , Me.Email, , , "Meeting Confirmation", strMsg
End Sub

Here are the fields I am using if it helps.

Is it also possible once the button is pressed to put in the current date against the fields Confirmation Date and have the email be sent from a generic account?

[June7]

Don't see anything wrong with the code syntax. Are you sure [Start Time] is included in the form RecordSource? Is that also name of textbox bound to that field?

Include current date by concatenating with Date() or Now() functions.

I don't know about sending from generic account. Probably not with SendObject method. Maybe with Outlook or SMTP automation.

Bing: access vba email generic account
http://stackoverflow.com/questions/1...-of-a-delegate

[BSJoJaMAx4]

I had to re add the start time field to the form and it seems to have fixed it. However, if I dont send the email (escape or close it) it returns to the form with a run-time error 2501. Can that be fixed?

[June7]

Use error handler code http://allenbrowne.com/ser-23a.html

Or just before the SendObject line:

On Error Resume Next