Link to home
Start Free TrialLog in
Avatar of Andrew Walker
Andrew Walker

asked on

Font of range

I am using the following code which sends an email from excel to outlook
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

Open in new window


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
Avatar of Neil Fleming
Neil Fleming
Flag of United Kingdom of Great Britain and Northern Ireland image

You seem to have a function called "RangeToHTML". What happens if you run Name_Lookup through that as well:

ie:
 Name_Lookup = RangeToHTML(ActiveSheet.Range("A3"))

Open in new window


(without the .Value)
Avatar of Andrew Walker
Andrew Walker

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
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
ASKER CERTIFIED SOLUTION
Avatar of Neil Fleming
Neil Fleming
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Neil, sorry about delay in reply I have been on Holiday, thank you for all your help it works like a charm!