Perspective blank To field from Excel VBA on some computers

Question

We have an Excel spreadsheet that we use to create quotes for potential customers. Using VBA, it creates PDF files and sends them to the client using Outlook. It takes the customer’s email address from the cell on the main sheet, where the user fills in the customer’s details.

A few weeks ago, he stopped filling out the client’s email address in the “To” field in Outlook, although it was filled out on the main sheet.

When we change anything in this spreadsheet, including code, we save it as a new “revision” (preserving all previous versions). Going back to previous versions, now I found that none of them work. It is strange as before. I am using Office 2016 (although I recently updated, but this problem is recent). The machine running Office 2013 also does not work. However, a machine running Office 2007 works.

Any ideas as to why this is a problem now, and why is it a problem only for certain versions of Office? Here is the code snippet:

    Private Sub send_as_pdf_Click()
    On Error GoTo ErrMsg
    Dim strPath As String, strFName As String
    Dim OutApp As Object, OutMail As Object

    strPath = Environ$("temp") & "\"

    strFName = Sheets("Quotation").Name & " " & Range("G18") & ".pdf"

    Sheets("Quotation").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        strPath & strFName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    strPath2 = Environ$("temp") & "\"

    strFName2 = Sheets("Quotation Offer Letter").Name & " " & Range("G18") & ".pdf"

    Sheets("Quotation Offer Letter").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        strPath2 & strFName2, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    strPath3 = Environ$("temp") & "\"

    strFName3 = Sheets("Additional Works Required").Name & " " & Range("G18") & ".pdf"

    Sheets("Additional Works Required").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        strPath3 & strFName3, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

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

    strbody = "<p style='color:#2C3E50;font-family:Calibri;font-size:11pt;'>Hi " & Range("C9") & ",</p>"

    strbody2 = "<p style='color:#2C3E50;font-family:Calibri;font-size:11pt;'>The content of the email goes here.</p>"

    On Error Resume Next
    With OutMail
        .Display
        .To = Range("C19")
        .CC = ""
        .BCC = "[e-mail]" & ";" & "[e-mail]" & ";" & Range("I6") & ";"
        .Subject = "Quotation"
        .HTMLBody = strbody & strbody2 & .HTMLBody
        .Attachments.Add strPath & strFName
        .Attachments.Add strPath2 & strFName2
        .Attachments.Add strPath3 & strFName3
        .Attachments.Add ("C:\Terms and Conditions of Business of Our Business.pdf")
        .Attachments.Add ("C:\Warranty Statement of Our Business.pdf")
    End With

    Kill strPath & strFName
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
Exit Sub

ErrMsg:
MsgBox ("MUST enter Issue Number, Date & Customer Info."), , "Customer Email Error Message"

End Sub

Answer 1

Try the following:

, , .

'On Error Resume Next

:

.To = Range("C19")

excel , Outlook, .

:

.To = Range("C19").Value2