Email From Form Using Access 97

[kmyoung]

Hi, Access Experts...

I have a form that is populated from a query. This form has both an email address field and a memo [body] field. I want to be able to click a button on the form and have it send individual emails to my Outlook outbox for each record within the query recordset, using the [body] field as the message text source. I would like to set the email format to HTML so that when the emails are generated, they utilize the default email stationery set within Outlook. Can someone tell me how to go about this?

Thanx in advance for your help.
Regards ;<)
Karen

 

[Hayley Baxter]

Hi Karen

Try this

Private Sub CmdEmail_Click()
Dim rsEmail As DAO.Recordset
Dim strEmail As String
Set rsEmail = CurrentDb.OpenRecordset("QrySendEmails")

Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("Email").Value
DoCmd.SendObject , , , strEmail, , , _
"Subject Here", _
"Good Morning" & _
vbCrLf & vbCrLf & [FieldName] & _
vbCrLf & vbCrLf & "Thanks" & _
vbCrLf & vbCrLf & "Best Regards" & _
vbCrLf & "Your Name" & _
vbCrLf & vbCrLf & "Signature" & _
vbCrLf & "Department" & _
vbCrLf & "Address Details" & _
vbCrLf & "Address Line 2" & _
vbCrLf & "city" & _
vbCrLf & "Post code" & _
vbCrLf & "Tel: Telephone no" & _
vbCrLf & "Fax: Fax no", False

rsEmail.MoveNext

Loop
Set rsEmail = Nothing

MsgBox "Emails have been sent"
End Sub

Let me know how you get on with that.

Hayley

 

[kmyoung]

Hi, Haley...

Still having some problems with this. Below is the code I used for the CmdEmail_Click event. When I click to send notices, it only creates one email although there are actually four records. Any idea what I'm doing wrong? Also, is there a way to have it just send to my Outlook Outbox rather than opening the draft message from the application? And, is there a way to set the format to HTML so that it will utilize my Outlook default stationery when sent to Outlook?


Private Sub CmdEmail_Click()
Dim rsEmail As DAO.Recordset
Dim strEmail As String
Set rsEmail = CurrentDb.OpenRecordset("qryIndividualNoticesDue")

Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("Email").Value
DoCmd.SendObject , , , strEmail, , , _
"Notice From Your OnBoarding Team", _
vbCrLf & vbCrLf & [Body] _

rsEmail.MoveNext

Loop
Set rsEmail = Nothing

MsgBox "Notices Have Been Sent"

End Sub

Thanx, Haley...looking forward to your reply.
Regards ;<)

 

[kmyoung]

Hi again, Hayley...

I'm still thinking through this and am wondering if this might work. If I set my query recordset in Groupby mode and have the email address field of the query semi-colon delinated so that all addresses are identified in a single field based on their group identity, it would only require one email per group.

Any thoughts on this?

Thanx Hayley for your earlier response. I really appreciate your help.

 

[Hayley Baxter]

Hi Karen

I've not had time to test your code yet but I will look into this for you tomorrow. Can you tell me which version of access you are using and I will create a sample for you.

If you are looking to send the emails without the draft first showing up, look at the code I posted above where I have set this to false. If it is set to true the email message first opens in outlook to preview before sending.

Not sure about the HTML format I will have to look into that as I've never used it but I would think it can be done.

Hayley

 

[kmyoung]

Access 97

Thank you so much Hayley...

I'm using Access 97. I'll look forward to hearing from you tomorrow. Have a great evening.

 

[kmyoung]

Sorry...Another Post

Hey, Hayley...

Thought I'd let you know that the code I sent you does appear to work. I thought it was only generating one email but, once you send or save the email that comes up first, the next one appears, and so on until it loops through all records. The problem I'm having though is that each record has it's own email text message derived from a field in the query recordset called [Body].

Is there a way to reset both the recipient and body before moving to the next record? Right now it generates a new email for all records but uses the same body for each.

Let me know if this is unclear. Again, Thanx

 

[Hayley Baxter]

Hmmm I see what you mean, now you really have given me a challenge.

If I can work this one out it would also be useful for me so I'll see if I can get anything to work unless you have anything to add Bukhix??

Hay

 

[Hayley Baxter]

Karen this sample might help with your HTML part. It's not quite doing everything you want just yet but we'll get there.

Have a look at this in the meantime.

Hay

 

[Hayley Baxter]

Just while we are on the subject - Bukhix if you are out there can you tell me how I can change the signature to take the name, title, phone, fax etc of the current user rather than this just being set. I know I would probably need a table with all the details etc but any help with the coding would be great.

Thanks for all your help on this.

Ta
Hayley

 

[kmyoung]

Thanx, Hayley...

This issue is driving me crazy. FYI, the following code generates an email for each record in the recordset, gives me the message that notices have been sent, and then opens a blank new message in html and uses the default stationery set in Outlook.

Private Sub CmdEmail_Click()
Dim appOutl As Object
Dim MyNameSpace As Object
Dim myemail As Object

Set appOutl = CreateObject("Outlook.Application")
Set MyNameSpace = appOutl.GetNamespace("MAPI")
Set myemail = appOutl.CreateItem(0)
Dim rsEmail As DAO.Recordset
Dim strEmail As String
Set rsEmail = CurrentDb.OpenRecordset("qryIndividualNoticesDue")

Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("Email").Value
DoCmd.SendObject , , , strEmail, , , _
"Notice From Your OnBoarding Team", _
vbCrLf & vbCrLf & [Body] _

rsEmail.MoveNext

Loop
Set rsEmail = Nothing

MsgBox "Notices Have Been Sent"
myemail.Display

End Sub

If I could just bring the messages onto that stationery and have the message text change based on the body field of the recordset, I'd have it made. Obviously the later is most important. I could live without the html if need be, but have to have the message text accurate.

Thanx so much for you help with this Hayley.

 

[kmyoung]

Hey, Hayley

I figured out the code so that it at least pulls in the correct body text...here it is

Private Sub CmdEmail_Click()
Dim appOutl As Object
Dim MyNameSpace As Object
Dim myemail As Object
Dim rsEmail As DAO.Recordset
Dim strEmail As String
Dim strBody As String

Set appOutl = CreateObject("Outlook.Application")
Set MyNameSpace = appOutl.GetNamespace("MAPI")
Set myemail = appOutl.CreateItem(0)

Set rsEmail = CurrentDb.OpenRecordset("qryIndividualNoticesDue")

Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("Email").Value
strBody = rsEmail.Fields("Body").Value
DoCmd.SendObject , , , strEmail, , , _
"Notice From Your OnBoarding Team", _
vbCrLf & vbCrLf & strBody _

rsEmail.MoveNext

Loop
Set rsEmail = Nothing

MsgBox "Notices Have Been Sent"
myemail.Display

End Sub

Now, if we can just get it to utilize the blank html formatted message that opens after the notices are sent, we'll be in good shape.

Will keep at it.

 

[Hayley Baxter]

Just while we are on the subject - Bukhix if you are out there can you tell me how I can change the signature to take the name, title, phone, fax etc of the current user rather than this just being set. I know I would probably need a table with all the details etc but any help with the coding would be great.

Can anyone shed some light on this. At present I currently have my own signature in the coding but would like to pick up the signature of the person who is sending the emails so that when the send button is chosen the signature will populate for that particular person. Not sure how I would go about this given that there may be more than one person in the database at any one time. I'm stumped on this one

Any help appreciated
Thanks

Hay