CODEWITHSUNDEEP
excelvbaoutlookemoji
Choofin
Choofin

Reputation: 11

Using VBA in Outlook to Send Texts with Emojis

I have some VBA code that I'm using to send texts from Outlook to team members of my project at work. For some background: for non-AT&T subscribers, we have no issue sending text messages from Outlook by plugging in peoples' numbers en masse into the To: field of Outlook emails. However, all AT&T subscribers will receive the text as a group message, which we want to avoid. The non-AT&T subscribers correctly receive individual texts when we do a group send.

We've written some VBA code to loop through a spreadsheet of AT&T numbers so that Outlook sends one email per AT&T number. This has been working fine for us, however, we were hoping to add some emojis into the texts that we're sending. I've done a lot of Googling and searching through stackoverflows questions, and I can't seem to find any code built for this purpose. I'm also a complete noob when it comes to VBA, and I've pieced this solution together thus far from getting help from a coworker and reading through threads on the internet. This bit about emojis has given me enough trouble that I thought I'd break down and submit this post.

For reference, here is my code:

Sub EmojiTest()
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim MobileNumber As String

      ' Create the Outlook session.
      Set objOutlook = CreateObject("Outlook.Application")
      Set xlApp = CreateObject("Excel.Application")

      'Grab list from Excel
      Set xlAtt = xlApp.Workbooks.Open("C:\Users\Username\Desktop\Spreadsheet with AT&T numbers.xlsx")
      xlAtt.Activate
      LastRow = xlAtt.ActiveSheet.Range("B" & xlAtt.ActiveSheet.Rows.Count).End(-4162).Row

      For i = 1 To LastRow
        xlAtt.Activate
        MobileNumber = xlAtt.ActiveSheet.Range("B" & i).Value

        ' Create the message.
        Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

        objOutlookMsg.SentOnBehalfOfName = "[email protected]"

        With objOutlookMsg
            ' Add the To recipient(s) to the message.
            Set objOutlookRecip = .Recipients.Add(MobileNumber)
            objOutlookRecip.Type = olTo

            ' Set the Subject, Body, and Importance of the message.
            .Subject = "Emoji Test"
            .Body = "Text with emojis"
            .Save
            .Send
        End With
      Next i
      Set objOutlook = Nothing
      xlApp.Workbooks.Close
      Set xlApp = Nothing
End Sub

This is code I never could have come up with myself due to my complete lack of experience with VBA, and limited experience coding in general. Any help is much appreciated.

Upvotes: 0

Views: 3112

Answers (3)

Uttam
Uttam

Reputation: 736

There is a solution at this link.

As per this, you paste smiley in an Excel cell and then read the cell value, it will be string with length 2, find the code of these 2 characters using AscW() and then chain them using Chrw, e.g. to have a smiley you can use ChrW(-10179) & ChrW(-8638).

Upvotes: 1

Choofin
Choofin

Reputation: 11

I got it!

Sub EmojiTest()
   Dim objOutlook As Outlook.Application
   Dim objOutlookMsg As Outlook.MailItem
   Dim objOutlookRecip As Outlook.Recipient
   Dim MobileNumber As String
   Dim strbody As String

      ' Create the Outlook session.
      Set objOutlook = CreateObject("Outlook.Application")
      Set xlApp = CreateObject("Excel.Application")

      'Grab list from Excel
      Set xlAtt = xlApp.Workbooks.Open("C:\Users\user\Desktop\Spreadsheet.xlsx")
      xlAtt.Activate
      LastRow = xlAtt.ActiveSheet.Range("B" & xlAtt.ActiveSheet.Rows.Count).End(-4162).Row

      For i = 1 To LastRow
        xlAtt.Activate
        MobileNumber = xlAtt.ActiveSheet.Range("B" & i).Value

        ' Create the message.
        Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

        objOutlookMsg.SentOnBehalfOfName = "[email protected]"
        strbody = "<BODY style=font-size:11pt;font-family:Segoe UI Symbol>&#127881;Congrats!<p>Paragraph 2.<p>Paragraph 3.</BODY>"
        With objOutlookMsg
            ' Add the To recipient(s) to the message.
            Set objOutlookRecip = .Recipients.Add(MobileNumber)
            objOutlookRecip.Type = olTo

            ' Set the Subject, Body, and Importance of the message.
            .Subject = "Emoji Test"
            .HTMLBody = strbody


            .Save
            .Send
        End With
      Next i
      Set objOutlook = Nothing
      xlApp.Workbooks.Close
      Set xlApp = Nothing
End Sub

Basically, I had to convert my message into HTML. I did that using, "Dim strbody As String" at the top, and then using ".HTMLBody = strbody" in my With statement. Once I did that, it was trivial to use the HTML hex code to enter in my emoji. Here is a page with the HTML hex code that I used (&#127881): http://www.fileformat.info/info/unicode/char/1f389/index.htm.

Learned a lot about using VBA doing this, so it was fun.

Thanks for your help Sam.

Upvotes: 0

user2087008
user2087008

Reputation:

Change:

.Body = "Text with emojis"

To:

.Body = "\ud83d\ude03"

Full list available here. Copy the box called Java escape string.

The \u escapes the unicode sequence, so typing "\u" and the UTF-16 sequence should let you insert any Emoji.

Some Emojis are actually 2 seperate char sequences, so you have to chain them together.

Upvotes: 2

Related Questions