Sending Email from Form

[Wet_Blanket]

Good Morning,

I have been searching through the forum the last couple of days for an issue I am dealing with.

I have a form that will be used to open new accounts. On the form, the user can select which items are missing, then click a button to send an email listing the items that are missing. I want to accomplish this by sending the info within the body of the email, and not attaching a report to the email.

Does anyone have any good resources I can use to accomplish this? I have done the email process before (but through the attachment only).

Thanks.

 

[Wolff]

I'm perhaps not using the best code for this function, but it does what I need it to do! I use a mix of VBA and HTML - the example below is shortened, but will give you a start point if you decide to use it! This is using Access 2000 with Outlook 2003.

Dim msgtxt As String
 Dim Summary As String
 Dim Destination As String
 Dim Sbject As String
 Dim CarbonCopy As String
 Dim objOutlook As Outlook.Application
 Dim objEmail As Outlook.MailItem

msgtxt = "Enter your html code in this section - you can include field names etc to pull data from your form"
Destination = "Whatever e-mail address or source field"
Sbject = "Whatever your subject should be - can be dependant on field in form"
CarbonCopy = "ENTER E-MAIL ADDRESS HERE"
Set objOutlook = CreateObject("Outlook.Application")
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
    .FlagDueBy = DateAdd("w", 2, Now())   'this gives a two day reminder
    .Importance = olImportanceHigh
    .To = Destination
    .CC = CarbonCopy
    .Subject = Sbject
    .HTMLBody = msgtxt
    .Display
    
End With

End Sub

I am by no means an expert with HTML or VBA, so thanks to the sources on the web that pushed me in this direction!! I generally just googled for sending e-mail with VBA and ended up using stuff from Excel forums that people had suggested!

ETA: You can also include images etc in the main body using HTML - I didn't include those examples but there are plenty of web pages explaining all the avialable html tags out there!

 

[Wet_Blanket]

Would that code, for the most part, work with Access 2003?

 

[Wolff]

I don't know any reason that it wouldn't.

Make sure you add in the Microsoft Outlook reference library within VBA tho!

 

[Wet_Blanket]

I don't know any reason that it wouldn't.

Make sure you add in the Microsoft Outlook reference library within VBA tho!

That part is over my head. I had to line out some items, but the code works great so far - except it sends the emails as 'low priority.' Ideally, I would like to do HTML code within the email, but that as well is over my head.

Is it possible to view the HTML code of an existing email, and just copy and paste it into the VBA code?

 

[Wolff]

If it's already working then you probably don't need to add the reference library. You can check this from within VBA by going to tools, references then just make sure there is a tick beside it.

I'm not the greatest with html either, but google was a wonderful tool for that! I've included a sample below which might help!

msgtxt = "<img src='\\location\of\image\and\name' alt='Alternative Name to be used if image not available' /><br><br>" & _
         "<font face='Arial'><font color=red><b>ENTER NAME HERE</font></b><br><br>" & _
         "Please complete the below with details of steps to be taken to stop this incident from happening again.<br>" & _
         "Please respond to this mailbox by <b><font color=red>ENTER DATE HERE</font></b><br><br>" & _
         "Kind Regards<br>IPM Service Management<br><br>" & _
         "<b>Incident Summary:</b><br> " & Me.Incident_summary & "<br><br>" & _
         "<b>Impact:</b> <br>" & Me.[Incident Impact] & "<br><br>" & _
         "<b>Final Root Cause Statement<b><br><br>" & _
         "<table border='1' align=Left width='800' frame='box'><tr><th><font color='Blue' face='Arial'>" & _
         "Root Cause Statement</font></th></tr><tr><td><font color=Red face='Arial'>" & _
         "Please enter your Root Cause Statement here</font></td></tr></table><br><br>" & _

To explain some of the html:
<br> is a break, or a new line
<b> is to start bold text
<tr> is a table row
<th> is a table header (so column names)
<td> is table data row
a / infront of any of these means to stop this format style, eg </b>
& _ is used to indicate that the code continues on the next line

Hopefully that will give you some ideas as to what you can use!

 

[Wet_Blanket]

Very cool. Thank you soo much.

What is the name of the google tool? Also, can you reference FORM items within the HTML code?

 

[Wolff]

Yep, you can refence form items, for example, the following line references a field on the form that the e-mail is being sent from :

"<b>Impact:</b> <br>" & Me.[Incident Impact] & "<br><br>"

The Me.[Incident Impact] is the form.field in this case.

There's not really a single tool that I found - I just used google to find some examples and explanations of the html tags. I guess you could possibly use Word to design your e-mail and save as html? You might then be able to view the html code (I can't test on this machine as that bit of office is not installed!)

 

[Wet_Blanket]

Wow! You've been awesome. Thank you very much - it appears that all is working now (at least for the time being). I'm sure when I start added the bells and whistles, I may be back.

 

[Wolff]

No problems - just post away if you need more help - if I can't help I am sure there will be people that can!

 

[Wet_Blanket]

I'm back already.

The set up is that the user will check off items on the form, and any items that are left unchecked will be included in the email. Is it possible to do an IIF statement within the HTML code?

Such as IIF([Me.field1] Is Null, [field1], " ")?

 

[Wolff]

I would suggest you declare a new variable, then set the variable outside of the html, and then use the variable in the html. Saves any confusion mixing html with IF statements etc! (that, and well, I wouldn't like to try it myself!!)

 

[Wet_Blanket]

Would that be something like;

dim reqdoc AS string

set reqdoc = IIF([field1] >5, "Pass", "Fail")

Then just reference reqdoc in the body?

 

[The_Doc_Man]

Several ways to do this. I would consider a string function to build your message text.

Pass in the recordset for the form so your code could check the items. If in doubt, the form's recordset is called Me.Recordset and it has every field that was defined in the table or query that was the basis for the form, whether or not you actually displayed that field.

If you had to navigate within the recordset, you also have a secondary reference to it called Me.RecordsetClone that would allow you to scan all records while leaving the form on the same "current" record. I.e., Me.Recordset.Clone.MoveFirst and other recordset methods work just fine on the "built-in" recordset clone.

Then you might just have code in your function that loops through your fields or records and says

TOP OF LOOP
If [This item is not checked] then
message text = message text + vbCrLf + "this item is added to the list."
end if
If not finished, then go back up to TOP OF LOOP.

If the list of items to be tested is completely within the current record, a loop isn't necessary and you can hard-code it all to test the individual fields and in that case you might not need the recordset at all, you could just use the form's controls.

Eventually, you return the message text you built (incrementally) as the value of the function when you reach the end of the recordset or other loop.

Or you can make that a subroutine and pass in the message text variable ByRef, which (I think) allows the message to be longer.

 

[Wolff]

Not sure if that would work or not, but then my VBA isn't amazingly good! The way I would do it is:

dim reqdoc as string

If me.field1 > 5 then
      set reqdoc = "True"
Else
      set reqdoc = "False"
End If

And then, yes, reference reqdoc in the html (as if you were referencing a field), so using previous example it would be:

"<b>Impact:</b> <br>" & reqdoc & "<br><br>"

ETA: Thanks Doc Man - getting past my capabilities now!!

 

[Wet_Blanket]

Thanks.

I put in below:

Dim reqdoc As String
If Me.tax_id Is Null Then
Set reqdoc = "Client's Tax ID Number"
Else
Set reqdoc = "None"
End If

But I get an "Object Required" error on the above red text spot.

 

[Wet_Blanket]

Several ways to do this. I would consider a string function to build your message text.

Pass in the recordset for the form so your code could check the items. If in doubt, the form's recordset is called Me.Recordset and it has every field that was defined in the table or query that was the basis for the form, whether or not you actually displayed that field.

If you had to navigate within the recordset, you also have a secondary reference to it called Me.RecordsetClone that would allow you to scan all records while leaving the form on the same "current" record. I.e., Me.Recordset.Clone.MoveFirst and other recordset methods work just fine on the "built-in" recordset clone.

Then you might just have code in your function that loops through your fields or records and says

TOP OF LOOP
If [This item is not checked] then
message text = message text + vbCrLf + "this item is added to the list."
end if
If not finished, then go back up to TOP OF LOOP.

If the list of items to be tested is completely within the current record, a loop isn't necessary and you can hard-code it all to test the individual fields and in that case you might not need the recordset at all, you could just use the form's controls.

Eventually, you return the message text you built (incrementally) as the value of the function when you reach the end of the recordset or other loop.

Or you can make that a subroutine and pass in the message text variable ByRef, which (I think) allows the message to be longer.

Wow, I think I have a little ways to go before I can grasp this.

 

[Wolff]

Thanks.

I put in below:

Dim reqdoc As String
If Me.tax_id Is Null Then
Set reqdoc = "Client's Tax ID Number"
Else
Set reqdoc = "None"
End If

But I get an "Object Required" error on the above red text spot.

Try it without set, so just reqdoc = "client's tax id number"