Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    May 2006
    Location
    Charlotte, North Carolina, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    HTML Emails (2003 SP2)

    I have created an email in Access, but now the client wants the emails to go out with colored text. I know you can do this with HTML formatting, but I'm not sure how to take my current code and transform it into HTML. The code is listed below:

    Sub Send_HTML_email()
    Dim rsEmailsToSend As ADODB.Recordset
    Dim objOLK As Object
    Dim olkMail As Object
    Dim strSubject As String

    Set rsEmailsToSend = New ADODB.Recordset
    Set objOLK = CreateObject("Outlook.application")

    rsEmailsToSend.Open "SELECT * from tbl_FOR_EMAIL;", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    If rsEmailsToSend.RecordCount < 1 Then Exit Sub
    rsEmailsToSend.MoveFirst

    With rsEmailsToSend
    Do Until rsEmailsToSend.EOF

    Dim Message
    If .Fields("Email Type") = 2 Then
    Message = "A PAF for a Promotion pay increase has been received for the Store Manager listed below. The amount" _
    & " of the increase is considered an EXCEPTION because it is outside of the Store Manager Salary Administration Guidelines. The" _
    & " EXCEPTION reason is listed below." _
    & Chr(10) & Chr(10) & "It is strongly suggested that you research this proposed increase and reply back via this e-mail " _
    & "WITHIN 7 BUSINESS DAYS if you approve or reject this exception(s) by using the voting buttons " _
    & "located at the top of this e-mail. " _
    & Chr(10) & Chr(10) & "If you approve of the exception(s) please e-mail your VP to provide an explanation of your " _
    & " approval. Please note that all exceptions should receive prior approval from your VP." _
    & Chr(10) & Chr(10) & " If you do NOT approve of the exception(s) please have (or direct) your DM / AOM to submit a " _
    & " correcting PAF. In order to reverse any increases that have been submitted, a new PAF will need " _
    & " to be completed. Your response to this e-mail will not generate a PAF to reverse the decision. " _
    & " As a reminder, the effective date of the change on the PAF must be the beginning of the next payroll period. " _
    & Chr(10) & Chr(10) & "For your reference, attached is a copy of the Store Manager Salary Administration Guidelines approved September 1, 2006." _

    Message = Message & Chr(10) & Chr(10) & "Employee Name: " & rsEmailsToSend![FULL_NAME] _
    & Chr(10) & "Job Title: " & rsEmailsToSend![Job_Name] _
    & Chr(10) & "Employee Number: " & rsEmailsToSend![Employee_Number] _
    & Chr(10) & "Store Number: " & rsEmailsToSend![STORE] _
    & Chr(10) & "Previous Pay: " & FormatCurrency(rsEmailsToSend![Previous SM Pay], 2) _
    & Chr(10) & "New Pay: " & FormatCurrency(rsEmailsToSend![New SM Pay], 2) _
    & Chr(10) & "% of proposed increase: " & FormatPercent(rsEmailsToSend![% of Proposed SM Increase], 2) _
    & Chr(10) & "Effective Change Date: " & rsEmailsToSend![Effective Change Date] _
    & Chr(10) & "Exception(s): Promotion or Special Adjustment Increase Exceeds 6%" _
    & Chr(10) & "DM/AOM: " & rsEmailsToSend![District_Mgr] _
    & Chr(10) & "Region: " & rsEmailsToSend![Region] _
    & Chr(10) & "District: " & rsEmailsToSend![District] _

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: HTML Emails (2003 SP2)

    I assume that you have a line

    olkMail.Body = Message.

    You must change Message to contain HTML tags, then change the above line to

    olkMail.HTMLBody = Message

    See for example HTML Tutorial for an introduction to HTML.
    Some examples:
    - paragraphs in HTML start with

    and end with </p>
    - a line break is indicated by <br>
    - formatting tags come in pairs, e.g. bold text
    - font properties: <font color=red><font color=red>red text</font color=red></font>

  3. #3
    New Lounger
    Join Date
    May 2006
    Location
    Charlotte, North Carolina, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HTML Emails (2003 SP2)

    the code has this: olkMail As Object. Do I need to change this?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: HTML Emails (2003 SP2)

    No. (It is in fact an Outlook.MailItem object, but as far as i can tell, you're using late binding.)

  5. #5
    New Lounger
    Join Date
    May 2006
    Location
    Charlotte, North Carolina, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HTML Emails (2003 SP2)

    Ok, I was finally able to get the emails in HTML format. Now I have been asked to add an image to the email. Do I use the HTML code [img]url[/img] to add the image, and if so, where in the code do I put it?
    <img src=/S/meltdown.gif border=0 alt=meltdown width=15 height=15>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: HTML Emails (2003 SP2)

    If you want to refer to a picture on the Internet, you can use the img tag. If you want to embed the image in the e-mail, you need rather complicated code - see To add an embedded image to an HTML message in Microsoft Outlook using code.

  7. #7
    New Lounger
    Join Date
    May 2006
    Location
    Charlotte, North Carolina, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HTML Emails (2003 SP2)

    I placed the image on the web and this seems to work great. The other code was definitely out of my skill set <img src=/S/smile.gif border=0 alt=smile width=15 height=15> Another issue that is popping up is the email pulls data from a table to populate the email. Before I changed the emails to HTML, the e-mail would list the fields in one column:

    Employee Name: SMITH,JOE
    Job Title: Store Manager.1.98.200
    Employee Number: 104931

    But now it's one long string. (Job Title: Store Manager.1.98.200 Employee Number: 451628 Store Number: 07363 ) I've tried to use the unordered list HTML code <ul>, but only the text in "" are effected. How do I get the text and data being pulled from the table on the same line and in the list format it was in previously? Sample code below:

    Message = Message & "Employee Name: " & rsEmailsToSend![FULL_NAME] _
    & "Job Title: " & rsEmailsToSend![Job_Name] _
    & "Employee Number: " & rsEmailsToSend![Employee_Number] _
    & "Store Number: " & rsEmailsToSend![STORE] _

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: HTML Emails (2003 SP2)

    I'm not entirely sure I understand what you mean, but as I mentioned before, use <br> to insert a line break.

  9. #9
    New Lounger
    Join Date
    May 2006
    Location
    Charlotte, North Carolina, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HTML Emails (2003 SP2)

    Edited by HansV to show tags correctly

    Ok. I was using <br>, </br> and I only needed to do <br>. That works great!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •