can you write a macro in Excel that integrates itself with Outlook to send multiple separate emails?

Factual Questions
1

I ‘think’ I’m just looking for a yes/no answer here. {ETA: so the “you” in the header just means the ‘general’ “you”}

I’m way beyond the age of needing you to “do my homework for me”, so if the answer is “yes, you can”, then I appreciate that and I’ll dig into it more and figure it out on my own (or hire some online help) ******.

For the teams that I’ve worked with in my career, I’ve usually been known as the “Excel guy” (though if you put me against the “best of the best” on this forum, I’d probably be around a 3 on a scale of 1 to 10). But I can certainly work with macros / pivot tables / crazy complex array formulas, etc - all within the umbrella of Excel.

My conundrum is this - I got into some hot water at work recently by sending out company-wide expense-report detail in an Excel file. In my mind there wasn’t anything covert about it. People at work spend money on what they need to spend money on, the company reimburses them, and that’s that.

{paraphrased example here} I sent one Excel file to Pam Beesly, Jim Halpert, and Michael Scott. That one file had Pam’s, Jim’s, and Michael’s expense-report detail all included in it. It had radio-buttons at the top that they could click next to their name, and their own expense-report detail would (via macro) automatically filter in Excel, ready-to-print.

Unfortunately, Pam got pissed that Jim could manipulate it to see what she was purchasing; Jim got pissed that Michael could see what he was purchasing, etc., on down the line. I hid the column that contained the names (as a token gesture for privacy, but you and I both know that anyone with a working knowledge of Excel could easily get around that).

The feedback I got was that I should have sent one file to each individual with just* their individual* expense-report detail. OK, fair enough.

But in my case, we’re talking about 30 - 35 people (so of course we’re getting way down into Bob Vance and Todd Packer territory now, but I digress).

Is there a way in Excel to write a macro that takes this one huge file on my screen with Pam’s / Jim’s / Michael’s / Bob’s / Todd’s expense-report detail, and with me pushing one single button it:

– filters out and deletes all the rows of everyone but Pam, and goes to Outlook and sends Pam that file
– returns to Excel and restores the file to include everyone, all rows
– filters out and deletes all the rows of everyone but Jim, and sends Jim that file
----- lather, rinse, repeat on down the line {end of my paraphrased example}

Again, if you tell me that “yes there is a way”, then it’s game on. I’ll try and figure it out. I’m not asking you to write me out any VBA script, or anything like that. I’ve tried watching a couple of Youtube videos, but they weren’t quite what I’m looking for.

****** All that being said, aren’t/weren’t there websites out there where you can “hire” someone for projects like this? It’s not worth my time if I have to send out 35 emails manually - we’ll come up with a work-around. And it’s not worth my money if we’re talkin’ 3-figures to hire someone to do this. (This was more a “process-improvement” thing, and it’s definitely not a hill that I need to die on).

2

The answer appears to be yes… I haven’t done what you ask but have gone the other way with writing a macro to allow users to cut and paste from Outlook into an Excel workbook.

Have a look at this link, it appears to cover the required concepts well and should give you an idea of the effort involved. One thing you’ll want to think about is that you will have to have an available table with the emplyee’s names as they appear in your workbook and the corresponding email addresses, you may then want your macro to copy the relevant information per person to a “clean” new excel workbook for each user to send as an email attachment and then delete on success, to ensure no chance of “leakage” between employees. Obviously you’ll want to put your own email address in the table while debugging.

3

Seems to me like all you’d need is a loop that fires off one email for each row, assuming each person is on a separate row in Excel.

I don’t write macros in Excel, but if I were in FileMaker it would look like this:

Loop
… Open URL [“mailto:” & Table::email & “?Subject=your expense reports”; no dialog]
… Go to Record [next; exit after last]
End Loop

(I’m also not an Outlook user but assuming it’s your designated email-protocol handler and allows other applications to tell it what to do (you need to authorize that), should work fine.

4

Sure, it’s doable.
Here’s one example , I always do this kind of thing by recording a macro where I go through the steps for one (filter table, create new worksheet, copy the filtered data in there, copy that to a new workbook, mail out that workbook, etc) and then parameterizing that recorded macro, usually with input from another control sheet so I can do the looping through that range.

5

There are several solutions available online for this. Some will split the main Excel sheet into multiple sheets based on the variable in a single column. Some will even integrate with Outlook and email that sheet as a separate file. The search term I used is “How to Split a sheet into multiple files based on a column in Excel”. Here is one such result How to split data into multiple worksheets based on column in excel 2013

6

Is your challenge splitting the sheet by employee, or controlling Outlook from Excel? Both are possible, but emailing is a little more challenging because of security concerns (side note - these restrictions were for the most part prompted by the Melissa virus in 1999).

The code itself is straightforward, it’s just a matter of getting the permissions correct. Look up how to create the Outlook object in VBA.

7

Absolutely. I’ve written an Excel macro do this for the purpose of reserving conference rooms at work.

The system won’t let us schedule a recurring appointment if there are any conflicts in the future, so my macro in Excel uses Outlook to schedule the meetings one by one for the room I choose, date/time, reminder, priority, etc.

Here’s the code, which refers to stuff on the worksheet:



Private Sub bBookMeetings_Click()
Dim a As Outlook.AppointmentItem
Dim wk As Worksheet
Dim i As Long
Dim j As Long
Dim k As Integer
Dim m As Long
Dim n As String

' Sun = 1, Mon = 2, Tue = 3, Wed = 4, Thu = 5, Fri = 6, Sat = 7
' olBusy, olFree, olOutOfOffice, olTentative

'---------------------------------------------
' Static variables to pull out of the loop
'---------------------------------------------
' user's frequency choice
j = Application.WorksheetFunction.VLookup(Range("J14"), Range("D43:E51"), 2, False)

' availability status
Select Case Range("J23")
    Case "Busy"
        m = olBusy
    Case "Free"
        m = olFree
    Case "OutOfOffice"
        m = olOutOfOffice
    Case "Tentative"
        m = olTentative
End Select
          
' required attendees
n = Application.WorksheetFunction.VLookup(Range("J7"), Range("A3:E39"), 5, False)
If Range("J9") <> "" Then n = n & ";" & Range("J9")
        


' Dates are just long integers, so this loop works nicely.
For i = Range("J11") To Range("J13")

    ' Deal with the days we want to do this...
    k = Weekday(i) ' weekday number for current day
    
    ' If it's an eligible day, then make it happen.
    If (j = k) Or (j = 8) Or ((j = 9) And (k >= 2) And (k <= 6)) Then
    
        Set a = Outlook.CreateItem(olAppointmentItem)
        
        a.RequiredAttendees = n
        
        a.Start = Format(i, "dd-mmm-yyyy") & " " & Format(Range("J16"), "h:mm AM/PM")
        a.Duration = Range("J17")
        
        a.Subject = Range("J19")
        a.Location = Range("J20")
        a.Body = Range("J21")
        
        a.BusyStatus = m
        
        a.ReminderMinutesBeforeStart = Range("J24")
        a.ReminderSet = (Range("J25") = "Yes")
                
        a.MeetingStatus = olMeeting
        a.ResponseRequested = 1
        
        If True Then
            a.Close olSave
            a.Send
        Else
            a.Save
            a.Display
        End If
        
        Set a = Nothing
        Set wk = Nothing

    End If ' eligible day

    Debug.Print i; Format(i, "dd-mmm-yyyy"), Format(i, "dddd"), Range("J14"), "Choice=" & j, "Weekday=" & k

Next i ' day counter loop

End Sub
8

Next time, please use meaningful and useful names for your variables and constants.

9

I would start by having the macro create a worksheet for each employee, either by looping through the report one line at a time or filtering the report by each name and copying the results to a separate worksheet. Looping is probably easier if you don’t know the names of the employees because you can create a new sheet for each new name you encounter; but if you know the names filtering is probably more efficient, I think. Then just call Outlook to email each worksheet to the named employee.

10

One “cheat” that you might consider…

Having the complete set of data is quite useful. But your cow-orkers* freak out about that. So… the complete set of data is hidden, but the macros display a subset showing only the individuals rows.

This is done by having the macro look up the signed-on User ID. Environ(“Username”) is the function. Then filter the complete set on that name and show it in a separate sheet.

And the last piece of the puzzle is how to hide the complete set of data. This is done in VBA by setting the worksheet’s Visible property to xlVeryHidden. Google that.

  • Dilbert. Late 90’s. But I like it.
11

Well, the code wasn’t meant for distribution, and follows normal alphabetic progression. And the code is short. There’s no confusion here. My longer code uses different naming conventions.

12

{OP here}

Thank you so much all you guys / (gals?) for the great feedback and ideas. Just to respond to the quote above - yeah, the ‘bitch of it’ seems to be “controlling Outlook from Excel”.

I’m gonna try all the suggestions listed here (gonna test them on a smaller scale with my close inner circle before I try the company-wide-distribution following April month-end close).

Thanks again for all the suggestions, much appreciated!

13

Very doable. There are quite a few ‘correct’ ways you can accomplish this.

What I did once with VBA attached to a Send button.

Create an individual workbook called [Username][ReportDate].xls from the user subset.
email the workbook to user
move workbook to archive folder
repeat