none
Strange characters with SendMail in Excel 2016 RRS feed

  • Question

  • Hi all,

    I have recently upgraded Office from 2007 to 2016. I use an Excel macro to send an email using the SendMail function:

    Set Destwb = ActiveWorkbook
    With Destwb
           .SendMail Recipients:=Array("email list...."), Subject:="Bla bla"
    End With

    However, the characters in the received email subject look very strange:

    The affected Excel macro used to work just fine before the upgrade.

    I have tried to repair my installation but that did not solve my problem. I have already asked the question in the "Excel IT Pro" forum (here), but no solution was offered and I was suggested to ask the question here.

    Can you please give me a hint on what the problem is and how to correct it?

    Regards


    • Edited by Valentin S Monday, March 12, 2018 11:04 AM
    Monday, March 12, 2018 11:03 AM

Answers

  • Hi Valentin S,

    I am using version below.

    I try to make a test with the above code on my side.

    I find that it is working properly on my side.

    Below is the output:

    I suggest you to check for any pending updates. If there are any new updates are available then install it.

    For a work around this issue, You can try to use Outlook to send mail.

    Sub Sample()
       'Setting up the Excel variables.
       Dim olApp As Object
       Dim olMailItm As Object
       Dim iCounter As Integer
       Dim Dest As Variant
       Dim SDest As String
    
       'Create the Outlook application and the empty email.
       Set olApp = CreateObject("Outlook.Application")
       Set olMailItm = olApp.CreateItem(0)
    
       'Using the email, add multiple recipients, using a list of addresses in column A.
       With olMailItm
           SDest = ""
           For iCounter = 1 To WorksheetFunction.CountA(Columns(1))
               If SDest = "" Then
                   SDest = Cells(iCounter, 1).Value
               Else
                   SDest = SDest &; ";" &; Cells(iCounter, 1).Value
               End If
           Next iCounter
    
        'Do additional formatting on the BCC and Subject lines, add the body text from the spreadsheet, and send.
           .BCC = SDest
           .Subject = "FYI"
           .Body = ActiveSheet.TextBoxes(1).Text
           .Send
       End With
    
       'Clean up the Outlook application.
       Set olMailItm = Nothing
       Set olApp = Nothing
    End Sub

    Reference:

    Sending Email to a List of Recipients Using Excel and Outlook

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Valentin S Wednesday, March 14, 2018 3:37 PM
    Tuesday, March 13, 2018 2:46 AM
    Moderator

All replies

  • Hi Valentin S,

    I am using version below.

    I try to make a test with the above code on my side.

    I find that it is working properly on my side.

    Below is the output:

    I suggest you to check for any pending updates. If there are any new updates are available then install it.

    For a work around this issue, You can try to use Outlook to send mail.

    Sub Sample()
       'Setting up the Excel variables.
       Dim olApp As Object
       Dim olMailItm As Object
       Dim iCounter As Integer
       Dim Dest As Variant
       Dim SDest As String
    
       'Create the Outlook application and the empty email.
       Set olApp = CreateObject("Outlook.Application")
       Set olMailItm = olApp.CreateItem(0)
    
       'Using the email, add multiple recipients, using a list of addresses in column A.
       With olMailItm
           SDest = ""
           For iCounter = 1 To WorksheetFunction.CountA(Columns(1))
               If SDest = "" Then
                   SDest = Cells(iCounter, 1).Value
               Else
                   SDest = SDest &; ";" &; Cells(iCounter, 1).Value
               End If
           Next iCounter
    
        'Do additional formatting on the BCC and Subject lines, add the body text from the spreadsheet, and send.
           .BCC = SDest
           .Subject = "FYI"
           .Body = ActiveSheet.TextBoxes(1).Text
           .Send
       End With
    
       'Clean up the Outlook application.
       Set olMailItm = Nothing
       Set olApp = Nothing
    End Sub

    Reference:

    Sending Email to a List of Recipients Using Excel and Outlook

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Valentin S Wednesday, March 14, 2018 3:37 PM
    Tuesday, March 13, 2018 2:46 AM
    Moderator
  • Hi Valentin,

    Did you use “Bla bla” for real test or it’s just place holder?

    In general, it is related with encode or the string is not valid to the received computer.

    First, I suggest you try SendMail with “123” as Subject, will this issue still exist?

    Second, I suggest you try SendMail with the email account on the same computer, will you see this issue?

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 13, 2018 5:27 AM
  • Thanks for the answer! Although it was not the direct solution to my problem, I implemented the workaround suggested by you and my problem is now solved.

    Regards

    • Edited by Valentin S Wednesday, March 14, 2018 3:43 PM
    Wednesday, March 14, 2018 3:39 PM
  • Thanks for the answer! The subject text is just a dummy. Any string will produce similarly strange characters. The SendMail function already sends from the default email account of the Windows user. Anyways I implemented the workaround suggested above.

    Regards

    Wednesday, March 14, 2018 3:43 PM
  • I too have this issue, but I need to send from Excel (2016), not Outlook - so can someone confirm this is a bug, and if so, when it will fixed or any work-a-rounds using Excel.

    My Macro is:

    Public Sub send_email()
      ThisWorkbook.SendMail "name@company.com "Test Subject"
    End Sub

    But subject contains strange character(s) when it is sent.  I also tried

    Public Sub send_email()
    
    With ThisWorkbook
        .SendMail Recipients:="name@company.com", Subject:="Test Subject"
    
    End With
    
    End Sub

    But both just have Euro character "€" as subject 


    Monday, March 23, 2020 3:07 PM
  • I found a solution:

    Public Sub SendEmail()
      
    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    ThisWorkbook.Save
    With OutMail
            .To = "name@company.com"
            .Subject = "Test Subject"
            .Attachments.Add ThisWorkbook.FullName
            .Send
        End With
        Set OutMail = Nothing
        Set OutApp = Nothing
    
    End Sub

    Even better, for my use case, is that I can use ".Display", rather than ".Send" and then I can add some comments in body of message before sending if I want to.

    • Proposed as answer by Mike Bounds Monday, March 23, 2020 3:47 PM
    Monday, March 23, 2020 3:47 PM