Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sending email with MS Access

Status
Not open for further replies.
tamtf

tamtf

Programmer
Jun 25, 2002
10
0
0
US
I am running the VBA code below from MS Access 97. We are trying to send to our customer an email with a html format in the body. When the body of this email is blank the code runs fine, but when I add the html code under Vbody it doesn't work. We are also trying to pull the Lease# from the form we use to send this email and add it to the body.
How can I do this? Thank you for helping me.
Tam

---------------
Function SendEmail()
Dim VFile As Variant
Dim i As Integer
Dim VDir, vBody, vTo, vSubject As String
Dim oOutlook As Outlook.Application
Dim oMessage As Outlook.MailItem
Set oOutlook = CreateObject("Outlook.Application")
Set oMessage = oOutlook.CreateItem(olMailItem)
vTo = "MyEmail@email.com"
vSubject = "Thank you for the order"
vBody = <html>
<head>
<title>Untitled Document</title>
<meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=iso-8859-1&quot;>
</head>
<body>
<p>Lease# & Forms![ScheduleMainForm]![lease#] & Order Status</p>
<p>We have repeatedly attempted to secure payment of the balance due of
$1,000 regarding the leases listed above. Your accounts have been
placed on FINAL DEMAND status for default of payment. We demand full payment
within ten days of receipt of this letter, or your account will be placed in
legal status for whatever action is necessary to obtain payment. </p>
</body>
</html>
With oMessage
.To = vTo
.Subject = vSubject
.Body = vBody & vbCrLf
.Display
End With

End Function
 
Sort by date Sort by votes
Hullo tamtf,

What errors are you seeing?

If it is just as easy to export the TO and put the message in a text file, I may have a solution, sometimes it is easier to solve things without a db than in one.

Try diminishing
vbody=

until it works and then build it back up again, or start with a really small vbody and expand it.






 
Upvote 0 Downvote
If I use a text file, do I have to send it as an attachment? I don't want to send out attachments to our customers.
The vbody is now as shown below and I still keep getting a syntax error.

Thanks,
Tam

vBody = <html><head><title>Untitled Document</title>
<meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=iso-8859-1&quot;>
</head>
<body>
<p>Lease# & Forms![ScheduleMainForm]![lease#] & Order Status</p><br>
<p>We have repeatedly attempted to secure payment of the balance due of</p>
</body>
</html>
 
Upvote 0 Downvote
Try bringing in a text file with the following p-code (using notation to cut down on lines.

Code: 
  function getafile$ (filname$)
    ff=freefile
    open filename$ for binary as ff
    message$ = space$(lof(ff))
    get #ff,,message$
    close ff
  getafile$ = message$
end function

This will jam whatever is in filename into a string variable and then you can use ordinary MAPI to pump it into a message.

Code: 
  . . .
 vBody = getfile$( &quot;C:\data\NowMessage.txt&quot;)
      With oMessage
          .To = vTo
          .Subject = vSubject
          .Body = vBody & vbCrLf
          .Display
      End With
  End Function


Try with a simple file first, a few lines, then add the HTML.

This is VB code, not tested in ACCXESS, should not be a problem I trust.

Lemme know if it is a prob.

J@roninsg.com
 
Upvote 0 Downvote
Try enclosing vBody between double quotes:

vBody = &quot;<html>&quot; _
& &quot;<head>&quot; _
& &quot;<title>Untitled Document</title>&quot; _
& &quot;<meta http-equiv=&quot; & Chr(34) & &quot;Content-Type&quot; _
& Chr(34) & &quot; content=&quot; & Chr(34) & &quot;text/html; charset=iso-8859-1&quot; & Chr(34) & &quot;>&quot; _
& &quot;</head>&quot; _
& &quot;<body>&quot; _
& &quot;<p>Lease# &quot; & Forms![ScheduleMainForm]![lease#] & &quot; Order Status</p>&quot; _
& &quot;<p>We have repeatedly attempted to secure payment of the balance due of&quot; _
& &quot; $1,000 regarding the leases listed above. Your accounts have been&quot; _
& &quot; placed on FINAL DEMAND status for default of payment. We demand full payment&quot; _
& &quot; within ten days of receipt of this letter, or your account will be placed in&quot; _
& &quot; legal status for whatever action is necessary to obtain payment. </p>&quot; _
& &quot;</body>&quot; _
& &quot;</html>&quot;

vBody will be built as a string and will contain all necessary tags when placed in the message body.
Note the Chr(34) used to replace the &quot; character. If you don't want to use Chr(34), you'll have to type &quot;&quot;&quot;&quot;&quot; (4 quotes) to pass one to the string.


Regards,

Dan
 
Upvote 0 Downvote
Dan, I suspect you are right. Good.

But a hardwired message?

Would it not be nice if VB had a metacommand

vbody= $textin
<html><body>
...


</body></html>
$\textin

so one could just type? Is it not easier to make it a file?

or a decent $include like it used to have?


Perhaps not. I wander. nice catch.

J
 
Upvote 0 Downvote
Well, my car would be much nicer if it had a satellite driven route tracker... but it hasn't, so I still have to figure the way myself and use old paper maps... LOL

But you're right, it would be really nice.

Dan
 
Upvote 0 Downvote
Thanks to both of you for your help. Doing some research I found the HTMLbody property(shown below) and now my code works fine. Jnicks thanks for the getfile function. :-D
Tamtf

vbody = getfile$(&quot;C:\temp\TestingFU.html&quot;)
or
vbody = &quot;<HTML><H2>My HTML page.</H2><BODY>My body.</BODY></HTML>&quot;

With oMessage
.To = vTo
.Subject = vSubject
.HTMLBody = vbody & vbCrLf
.Display
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Johnny S
  • Locked
  • Question
Create folder based on ID field MS Access
Replies
3
Views
93
MattGreer
MattGreer
Dan Stoica
  • Locked
  • Question
Problems with Foxpro dsnless linked tables
Replies
0
Views
76
Dan Stoica
Dan Stoica
bobbyer
  • Locked
  • Question
Ms Access VBA Automatic Relinking Frontend to 2 Backend via form
Replies
13
Views
187
bobbyer
bobbyer
bobbyer
  • Locked
  • Question
ms accesss option group on report
Replies
4
Views
76
bobbyer
bobbyer
Kim296
  • Locked
  • Question
Sending Gmail using vba Send error
Replies
6
Views
240
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top