VBA HTML email code not working in 2010

T

Twaddy006

New Member
Joined
Apr 12, 2014
Messages
26
Afternoon all

I have a User Form which when ran should generate and display an Outlook email with a distribution list and the contents of the form in to the body of the mail. It was built on an XP machine running Office 2003 and worked perfectly, we're now on Win 7 at work running 2010 and it no longer works as it should.

The email opens, populates all names in the email distribution list and adds the email subject, however the body of the mail is blank. I'm afraid my VBA knowledge is limited so I'm not able to work out why it no longer works, I'd very much appreciate your thoughts on the below or if you know of any other reason why it would stop working in 2010

I notice when previewing the post it's formatted the HTML code rather than showing the actual code, such as bold text, it's also removed the line break code, just thought I'd flag it in case anyone was wondering. Apologies if it's due to how I've posted it

Many thanks in advance

Twaddy
Code: 
[COLOR=#000000][FONT=Arial]Sub EmailMeNewGroup()[/FONT][/COLOR][COLOR=#000000][FONT=Calibri][FONT=Arial]<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]Dim Cell     As Range<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    Dim MailList As Variant<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    Dim Rng      As Range<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    Dim Wks      As Worksheet<o:p class=""></o:p>[/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Calibri][FONT=Arial]    Dim OlApp    As Object<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    Dim OlMail   As Object<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    Dim OlNS     As Object<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    Dim HTMLbody As String<o:p class=""></o:p>[/FONT][/FONT][/COLOR]


[COLOR=#000000][FONT=Calibri][FONT=Arial]        Set Wks = Workbooks("Change Notification Form 2.3.xlsm").Sheets("Email List")<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]        Set Rng = Wks.Range("A1", Wks.Cells(Rows.Count, "A").End(xlUp))<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]        <o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]        For Each Cell In Rng<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]            MailList = MailList & Cell.Value & ";"<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]        Next Cell<o:p class=""></o:p>[/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Calibri][FONT=Arial]        Set OlApp = CreateObject("Outlook.Application")<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]        Set OlNS = OlApp.Session.GetDefaultFolder(6)<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]        <o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    Dim iOrderIT As String, iCustomer As String, iText34 As String, iChangeType As String<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    Dim iArea1 As String, iArea2 As String, iText3 As String, iText4 As String, iText5 As String, iText8 As String, iText9 As String<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    Dim iText12 As String, iComboBox3 As String<o:p class=""></o:p>[/FONT][/FONT][/COLOR]


[COLOR=#000000][FONT=Calibri][FONT=Arial]        iOrderIT = UserFormChangeNotification.TextBox1.Text<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]        iCustomer = UserFormChangeNotification.TextBox2.Text<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]        iText34 = UserFormChangeNotification.TextBox34.Text<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]        iChangeType = UserFormChangeNotification.ComboBox1.Text<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]        iArea1 = UserFormChangeNotification.ComboBox2.Text<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]        iArea2 = UserFormChangeNotification.ComboBox3.Text<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]        iText3 = UserFormChangeNotification.TextBox3.Text<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]        iText4 = UserFormChangeNotification.TextBox4.Text<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]        iText5 = UserFormChangeNotification.TextBox5.Text<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]        iText8 = UserFormChangeNotification.TextBox8.Text<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]        iText9 = UserFormChangeNotification.TextBox9.Text<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]        iText12 = Replace(UserFormChangeNotification.TextBox12.Text, Chr(10), "
")<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]        iComboBox3 = UserFormChangeNotification.ComboBox3.Text<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]        <o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]   HTMLbody = "[FONT=Arial][SIZE=2][COLOR=#000000]" & "Hello all" & "
" & "
" & "Please find below details of a scheduled change" _<o:p class=""></o:p>[/COLOR][/SIZE][/FONT][/FONT][/FONT]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    & "
" & "
" & "OrderIT Reference :" & " " & iOrderIT _<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    & "
" & "Customer :" & " " & iCustomer _<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    & "
" & "[B]Scheduled Change Date :[/B]" & " " & iText34 _<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    & "
" _<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    & "
" & "[B]Change Type :[/B]" & " " & iChangeType _<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    & "
" _<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    & "
" & "Area :" & " " & iArea1 _<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    & "
" & "Group Name :" & " " & iText3 _<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    & "
" & "Supergroup :" & " " & iText4 _<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    & "
" _<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    & "
" & "[B]Additional Information :[/B]" _<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    & "
" & iText12 _<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    & "
" _<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    & "
" & "Please email $TB - Telephony Routing Support & Solutions if you wish to unsubscribe" _<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    & "
" _<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    & "
" & "Many thanks"<o:p class=""></o:p>[/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Calibri][FONT=Arial]On Error Resume Next<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]            With OlApp.CreateItem(0)<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]                .display<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]                .to = MailList<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]                .CC = ""<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]                .BCC = ""<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]                .Subject = "Telephony Routing Change Notification" & " " & "-" & " " & "New Group"<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]                .HTMLbody = HTMLbody & "
" & .HTMLbody<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]                .display<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]            End With<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    <o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    Set OlMail = Nothing<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    Set OlApp = Nothing<o:p class=""></o:p>[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][FONT=Arial]    <o:p class=""></o:p>[/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Arial]End Sub[/FONT][/COLOR][/COLOR]

 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
You must log in or register to reply here.

Similar threads

B
  • Solved
VBA Email Outllook Item .Send does not work
Replies
3
Views
890
Anfinsen
A
L
  • Question Question
What's wrong with this VBA code string to email out a picture of a range from Excel? It used to work before
Replies
3
Views
1K
Logit
Logit
Jyggalag
  • Solved
Attach subject of email to a cell value?
Replies
2
Views
969
Jyggalag
Jyggalag
Jyggalag
  • Solved
Change my email macro to email multiple recipients in different rows
2
Replies
10
Views
1K
RoryA
RoryA
T
  • Question Question
VBA code to produce Outlook mail not adding auto signature
Replies
6
Views
2K
Twaddy006
T

Forum statistics

Threads
1,224,603
Messages
6,179,854
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top