Sub EmailWelcome() Dim FirstName As String, Subj As String, LineManager As String Dim GPAccess As String, POlevel As String, Email As String Dim Msg As String, URL As String Dim myHelpLink As String Dim r As Integer, x As Double ' Get the email address Email = Sheets(1).Range("C14") FirstName = Sheets(1).Range("C5") LineManager = Sheets(1).Range("C10") GPAccess = Sheets(1).Range("C37") POlevel = Sheets(1).Range("C38") ' Message subject Subj = "Welcome - ICT Information " ' Compose the message Msg = "" Msg = Msg & "Dear " & FirstName & "," & vbCrLf & vbCrLf Msg = Msg & "Hello and welcome to the team!" & vbCrLf & vbCrLf Msg = Msg & "We have put some important information about our IT systems on the Intranet for you" & "." & vbCrLf & vbCrLf 'Msg = Msg & "" & "http://intranet/People/CorporateServices/IT/New to Us/Home.aspx" & "" & vbCrLf & vbCrLf Msg = Msg & "Here you will find important information about passwords (which password for which system), working from home and backing up your data correctly. For example, it is your responsibility to back up files to the server/Intranet, and we will look after them from there. " & vbCrLf & vbCrLf Msg = Msg & "It will only take about 10 minutes to read all the pages on the link above, and it will clear up some of the questions you have." & vbCrLf & vbCrLf Msg = Msg & "Just email us if there is anything that we can help you with (related to ICT or Facilities) after checking the links above." & vbCrLf & vbCrLf Msg = Msg & "We hope that this is the start of a long and positive time working together. " & vbCrLf & vbCrLf 'Msg = Msg & Cells(r, 3).Text & "." & vbCrLf & vbCrLf Msg = Msg & "Kind Regards" & vbCrLf Msg = Msg & "The IT and Facilities Team" ' Replace spaces with %20 (hex) Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20") Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20") ' Replace carriage returns with %0D%0A (hex) Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A") ' Create the URL URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg ' Execute the URL (start the email client) ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus ' Wait two seconds before sending keystrokes Application.Wait (Now + TimeValue("0:00:02")) ' Application.SendKeys "%s" End Sub
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
22 Experts available now in Live!