Andrew Walker
asked on
Font of range
I am using the following code which sends an email from excel to outlook
The issue I am having is once the email is generated within outlook the font of Name_Lookup shows as Times new roman not basic commercial as the rest of the template is in. how do I change this? I hope this makes sense
Sub Send_Email_Late()
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim Name_Lookup As String
Set rng = Nothing
Name_Lookup = ActiveSheet.Range("A3").Value
On Error Resume Next
'Only the visible cells in the selection
'Set rng = Sheets("Sheet 1").Range("B2:B22").SpecialCells(xlCellTypeVisible)'
'You can also use a fixed range if you want
Set rng = Sheets("Stationary").Range("B1:O8").SpecialCells(xlCellTypeVisible) ':B11
On Error GoTo 0
If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
'.SentOnBehalfOfName = "sales@matthewclark.co.uk"
.To = ActiveSheet.Range("Cs4").Value
'.CC = Sheets("Email Data").Range("K2").Value'
.BCC = ""
.Subject = "Lateness Email - ENTER DATE"
.HTMLBody = Name_Lookup & RangetoHTML(rng)
.Display 'or use .Display
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
' MsgBox "The CS Form has successfully been sent to MC Customer Services. A copy has been saved in your sent email folder.", vbInformation
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
The issue I am having is once the email is generated within outlook the font of Name_Lookup shows as Times new roman not basic commercial as the rest of the template is in. how do I change this? I hope this makes sense
ASKER
Hi Neil
I amended the code as per your suggestion, and it does not pick up the value that is in A3, (which is the persons name) but does pull through the rest of the range from the stationary sheet
I amended the code as per your suggestion, and it does not pick up the value that is in A3, (which is the persons name) but does pull through the rest of the range from the stationary sheet
ASKER
Hi Neil
I have attached I version of the file so you can see how it works, which may make it easier? I have only put the macro in the late card which can be selected in the drop down in A1 the code is in module 2
Performance-Cards.xlsm
I have attached I version of the file so you can see how it works, which may make it easier? I have only put the macro in the late card which can be selected in the drop down in A1 the code is in module 2
Performance-Cards.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Neil, sorry about delay in reply I have been on Holiday, thank you for all your help it works like a charm!
ie:
Open in new window
(without the .Value)