Excel VB to create and send an outlook appointment with html code

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

I have started writing the code for what I want done. I have a worksheet with 40 columns and 200 rows. I can get all the data I want and open up an outlook appointment and even add an attachment. I am having issues of trying to make the body of the text html (change some words to red and bold them). When I change the line ".Body = email_Msg & vbCrLf & vbCrLf & Signature", it doesn't allow any formatting. I am having some issues. Can you please help?

Matt



Sub Welcome_Note()

Dim sRange As Range
Dim sEndRow As Integer
Dim sFirst_Name As String
Dim sLast_Name As String
Dim sFull_Name As String
Dim sCAI As String
Dim sWhat_Hire As String
Dim sJob_Type As String
Dim sJob_Title As String
Dim sStart_Date As String
Dim sPersonal_Email As String
Dim sSupervisor As String
Dim sFileName As String

sFileNameZip = "C:\Users\Help.xls"

Dim OutApp As Object
Dim OutMail As Object

Set sRange = ActiveCell
sEndRow = sRange.Row

sFirst_Name = ActiveSheet.Range("N" & sEndRow)
sLast_Name = ActiveSheet.Range("O" & sEndRow)
sFull_Name = sFirst_Name & " " & sLast_Name
sCAI = ActiveSheet.Range("Q" & sEndRow)
sWhat_Hire = ActiveSheet.Range("R" & sEndRow)
sJob_Type = ActiveSheet.Range("S" & sEndRow)
sJob_Title = ActiveSheet.Range("T" & sEndRow)
sStart_Date = ActiveSheet.Range("V" & sEndRow)
sPersonal_Email = ActiveSheet.Range("AC" & sEndRow)
sSupervisor = ActiveSheet.Range("AD" & sEndRow)


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

email_Msg = "<B>Hello " & sFirst_Name & ":" & vbCrLf & vbCrLf & "Congratulations and welcome to the Big A Team. My name is Michael "
email_Msg = email_Msg & "and I will assist you. In the unlikely event I am out of the office on your first day, please "
email_Msg = email_Msg & "contact my back up Jennifer. Your key information is below:" & vbCrLf & vbCrLf




email_Msg = email_Msg & "First Day Itinerary:" & vbCrLf & "8:00 am - Meet at the 1500 Louisiana Main Lobby Security Desk "
email_Msg = email_Msg & "(ask the security to contact me)" & vbCrLf &"8:00 am - Obtain a Smartbadge and Activate" & vbCrLf
email_Msg = email_Msg & "9:00 am - Complete administrative items, e.g.., Direct Deposit, travel and expense" & vbCrLf
email_Msg = email_Msg & "11:30 am - Lunch" & vbCrLf & "12:45 am - Continue administrative" & vbCrLf
email_Msg = email_Msg & "1:00 pm - I-9 verification (please bring twoforms of U.S. identification)" & vbCrLf
email_Msg = email_Msg & "2:00 pm - Review Learning Management System (LMS) - Analiza Mckewen" & vbCrLf
email_Msg = email_Msg & "4:30 pm - End of first day" & vbCrLf & vbCrLf
email_Msg = email_Msg & "Hotels:" & vbCrLf & "Crown Plaza" & vbCrLf &"300 Anywhere St" & vbCrLf & "Houston, TX 77002" & vbCrLf & "(713) 888-8888" & vbCrLf & vbCrLf
email_Msg = email_Msg & "Hyatt Downtown" & vbCrLf & "1200 Overthere St" & vbCrLf & "Houston, TX 77002" & vbCrLf
email_Msg = email_Msg & "(713) 888-8888" & vbCrLf & vbCrLf & "I look forward to meeting you!"



On Error Resume Next
With OutMail
.Recipients.Add "(e-mail address removed)"
.Subject = "Welcome" & sFull_Name
.Location = "420 Overthere St, Houston, TX 77002 (Security Desk /Ground Floor Lobby)"
.Start = sStart_Date & " 8:00:00 AM"
.Duration = 510
.AllDayEvent = "False"
.MeetingStatus = olMeeting
.Attachments.Add sFileNameZip
.Body = email_Msg & vbCrLf & vbCrLf & Signature
.Display 'or use .Send
End With



End Sub
 
Hi Matt

Not tested, sorry (no Outlook on my private computers)

With OutMail
'... replace ".Body = ... " with
.HTMLBody = "<HTML><BODY><B>Bold text</B> plain text.</BODY></HTML>"
'...
End With

theory on
http://msdn.microsoft.com/en-us/library/office/aa171418(v=office.11).aspx

HTH. Best wishes Harald

"Matt" <[email protected]> skrev i melding
I have started writing the code for what I want done. I have a worksheet
with 40 columns and 200 rows. I can get all the data I want and open up an
outlook appointment and even add an attachment. I am having issues of
trying to make the body of the text html (change some words to red and bold
them). When I change the line ".Body = email_Msg & vbCrLf & vbCrLf &
Signature", it doesn't allow any formatting. I am having some issues. Can
you please help?

Matt

(SNIP)

With OutMail
.Recipients.Add "(e-mail address removed)"
.Subject = "Welcome" & sFull_Name
.Location = "420 Overthere St, Houston, TX 77002 (Security Desk /
Ground Floor Lobby)"
.Start = sStart_Date & " 8:00:00 AM"
.Duration = 510
.AllDayEvent = "False"
.MeetingStatus = olMeeting
.Attachments.Add sFileNameZip
.Body = email_Msg & vbCrLf & vbCrLf & Signature
.Display 'or use .Send
End With
 
Back
Top