Solved: Email From Access

[whanana]

I am trying to send an email from Access with a button, pulling certain information from the Access fields. I am lazy, I don?t want to have to interact with it all ? click the button and off it goes. I saw the KB entry on sending an email from excel, and I've tried to adapt it as best I can.



As of yet, I do not have a field in my database for an email address, but can easily add one and modify my forms, if needed. I want the macro to create and sent an email which does the following:

?add the lastname and firstname field data to the TO: for the current record. (we have an address book that should recognize the name as long as there is a comma in between.
?Add the Problem description field data to the Message body for the current record
?Add a Subject Line that says ?Your trouble ticket has been completed.? And append the Ticket # to the beginning or the end from the Ticket ID field.

The issue I am having is pulling data from the access database to insert into the email. What I have so far, is as follows:

Function SendEmail()
Dim OL As Object
Dim EmailItem As Object
Dim Wb As Workbook
Dim EmailAddress As String

Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
EmailAddress = ?joe.dirt@mycompany.com"
With EmailItem
.Subject = "Your trouble Ticket has been completed"
.Body = "" 'Reference my access field
.To = EmailAddress
.Importance = olImportanceNormal
'.Attachments.Add 'I don't need an attachment

.Send
End With
Set OL = Nothing
Set EmailItem = Nothing
End Function

[OBP]

Assuming that you are working from a Form that has the required Table Record displayed then you can transfer the data from the Form to the Email either directly or by using a string variable like the "EmailAddress" in your code.
To transfer the Form's Fields to the VBA use the
Me.FieldName
Where FieldName is the Actual Name of your Form's Field.
So to get the First and Last Name you could use

Dim WholeName as String
WholeName = Me.FirstName & ", " & Me.LastName

Assuming that your Field names are FirstName and LastName.

The same applies to your TicketID field, use

Me.TicketID
or if there is a space
Me.Ticket_ID

The VBA Wizard will list the Controls and Fields on the form as soon as you type in
Me.

[asingh]

Hi,

If you want data from a table..and based on that create a sequence that will send out mails..till the table count is exhausted..this can be done.

1.Push all the mail related data into a normalized table.
2.Open a recordset.
3. Select all the records from the table into the recordset.
4. Create a loop based on the recordset COUNT.
5. Inside the loop place your mail sending code.
6. Assign values from your selected recordset to the mail fields..as per your code.

By the way..is the mail sending code working fine..i did not look into it in detail...

Let me know..I can supply the code..for all of the above...!

regards
asingh

[whanana]

Awesome guys, thanks for the help. The first version I posted worked, it just didn't pull data from access. The semi-final version I wrote up is below, FYI.



Thanks again, you guys are great.



Function SendEmail()

Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblClosedTicketEmail")
MyRS.MoveFirst
'Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
'Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![email]
With objOutlookMsg
'Add the To Recipient to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)

'Add the CC Recipients to the e-mail message.
'If (IsNull(Forms!frmSendEmail!FirstName)) Then
'Else
' Set objOutlookRecip = .Recipients.Add(Forms!frmClosedTicketEmail!FirstName)
' objOutlookRecip.Type = olCC
'End If

'Set the Subject, the Body, and the Importance of the e-mail message.
.Subject = "Your trouble ticket has been completed!"
.Body = MyRS![ProblemDescription]
'.Importance = olImportanceHigh 'High Importance
'Add the attachment to the email message.
'If not IsMissing(AttachmentPath) Then
'Set objOutlookAttach = .Attachments.Add(AttachmentPath)
'End If

'Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Function