Customizing an email button macro

Gingertrees

Well-known Member
Joined
Sep 21, 2009
Messages
697
I created an email button on my excel workbook that I distribute to my colleagues to send in to me. They fill out one of these forms for each customer they sign up for a new program; among the info gathered is our internal customer ID, business name, and contact info. The Customer ID and business name are both defined names in my workbook and appear on the same sheet as the email button.

What I'd like to do:
have the subject of this email include the customer ID and business name, plus some standard text.
So if the Customer ID is AB123, and the business name is Widgetco, I want my email to have the subject
"AB123 Widgetco program enrollment"

NOTE: If someone can only figure out the Windows side of this question, that's totally cool! I don't work with too many Macs. Don't let that stop you from responding if you can answer half my question. Thanks!

Here's my email button code (thanks to Ron DeBruin's helpful site for this) :
Code:
Private Sub CommandButton3_Click() 'email button
'Should work in 97-2010
'Test the OperatingSystem
    ActiveWorkbook.Save
    
    If Not Application.OperatingSystem Like "*Mac*" Then
        'I am Windows
        Call WindowsEmailButton
    Else
        'I am a Mac and will test if it is Excel 2011 or higher
        If Val(Application.Version) > 14 Then
            Call MacEmailButton
        End If
    End If
End Sub


Sub WindowsEmailButton()
'!!!!must have a desktop email application (Outlook, LocustNotes, etc)!!!!
    Dim wb As Workbook
    Dim I As Long


    Set wb = ActiveWorkbook


    If Val(Application.Version) >= 12 Then 'Excel 2007 or later
    'Excel 2007 = 12, 2010 = 14, Mac 2011 = 14.1 or 14,1, pre-2007 = 8-11
        If wb.FileFormat = 51 And wb.HasVBProject = True Then
            MsgBox "There is VBA code in this xlsx file, there will" & vbNewLine & _
                   "be no VBA code in the file you send. Save the" & vbNewLine & _
                   "file first as xlsm and then try the macro again.", vbInformation
            Exit Sub
        End If
    End If


    On Error Resume Next
    For I = 1 To 3
        wb.SendMail "me@email.com, jeff@email.com", _
                    "Program Enrollment"
        If Err.Number = 0 Then Exit For
    Next I
    On Error GoTo 0
End Sub


Sub MacEmailButton()
'!!!!must have Outlook for Mac!!!!
'For Excel 2011 for the Mac and Outlook 2011
'Note: The workbook must be Saved ones
    Dim wb As Workbook


    If Val(Application.Version) < 14 Then Exit Sub


    Set wb = ActiveWorkbook
    With wb
        MailFromMacwithOutlook bodycontent:="Program enrollment", _
                    mailsubject:="Program Enrollment signup mac", _
                    toaddress:="me@email.com, jeff@email.com", _
                    ccaddress:="", _
                    bccaddress:="", _
                    attachment:=.FullName, _
                    displaymail:=False
    End With
    Set wb = Nothing
End Sub
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Jim Gordon Mac MVP

Board Regular
Joined
Jul 22, 2011
Messages
240
The subject is omitted in the message due to a bug. I've reported this bug and you can too. Go to the Excel Help menu and choose Send Feedback to display the suggestion form. You should describe the bug and suggest that it be fixed.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,795
Messages
5,598,083
Members
414,210
Latest member
Janisogor

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top