Reply
 
Thread Tools Display Modes
  #1  
Old 04-16-2019, 11:42 PM
Win Place Show is offline
Guest
 
Join Date: Feb 2011
Posts: 255

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


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).

Last edited by Win Place Show; 04-16-2019 at 11:46 PM.
  #2  
Old 04-17-2019, 01:02 AM
moes lotion is offline
Guest
 
Join Date: Nov 2002
Location: North of a Great Lake
Posts: 224
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  
Old 04-17-2019, 02:06 AM
AHunter3 is offline
Charter Member
 
Join Date: Mar 1999
Location: NY (Manhattan) NY USA
Posts: 20,136
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  
Old 04-17-2019, 04:09 AM
MrDibble's Avatar
MrDibble is offline
Guest
 
Join Date: Mar 2001
Location: Cape Town, South Africa &
Posts: 25,035
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.

Last edited by MrDibble; 04-17-2019 at 04:13 AM.
  #5  
Old 04-17-2019, 11:33 AM
ashtayk is offline
Guest
 
Join Date: Aug 2000
Location: Houston, TX
Posts: 297
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 https://social.msdn.microsoft.com/Fo...forum=exceldev
  #6  
Old 04-17-2019, 11:55 AM
TroutMan's Avatar
TroutMan is online now
Guest
 
Join Date: Sep 2008
Location: Portland, OR
Posts: 4,599
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.

Last edited by TroutMan; 04-17-2019 at 11:56 AM.
  #7  
Old 04-18-2019, 07:21 AM
Balthisar is offline
Charter Member
 
Join Date: Nov 2000
Location: Southeast Michigan, USA
Posts: 11,003
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:

Code:
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  
Old 04-18-2019, 08:47 AM
Quartz's Avatar
Quartz is online now
Charter Member
 
Join Date: Jan 2003
Location: Where the haggis roam fre
Posts: 30,831
Quote:
Originally Posted by Balthisar View Post
Absolutely. I've written an Excel macro do this for the purpose of reserving conference rooms at work.
Next time, please use meaningful and useful names for your variables and constants.
  #9  
Old 04-19-2019, 02:41 PM
Skammer's Avatar
Skammer is offline
Charter Member
 
Join Date: Aug 2002
Location: Music City USA
Posts: 14,187
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  
Old 04-19-2019, 03:01 PM
K364's Avatar
K364 is offline
Charter Member
 
Join Date: Nov 2001
Location: Edmonton, Alberta
Posts: 2,673
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  
Old 04-19-2019, 06:12 PM
Balthisar is offline
Charter Member
 
Join Date: Nov 2000
Location: Southeast Michigan, USA
Posts: 11,003
Quote:
Originally Posted by Quartz View Post
Next time, please use meaningful and useful names for your variables and constants.
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  
Old 04-19-2019, 09:32 PM
Win Place Show is offline
Guest
 
Join Date: Feb 2011
Posts: 255
{OP here}

Quote:
Originally Posted by TroutMan View Post
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.
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  
Old 04-19-2019, 09:34 PM
Projammer's Avatar
Projammer is offline
Member
 
Join Date: Apr 2006
Location: SW Arkansas
Posts: 6,575
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
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 09:42 AM.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.

Send questions for Cecil Adams to: cecil@straightdope.com

Send comments about this website to: webmaster@straightdope.com

Terms of Use / Privacy Policy

Advertise on the Straight Dope!
(Your direct line to thousands of the smartest, hippest people on the planet, plus a few total dipsticks.)

Copyright 2018 STM Reader, LLC.

 
Copyright © 2017