Macro/VBA Email & File creation

**shane.albrandt** · 05-28-2010, 02:36 PM

ATTACHED IS A SAMPLE FILE

Hi everyone!

I have Excel 2003. I've got a list of all our employees and who they report to (their managers) and that manager's email address. My ultimate goal is to send each manager an email weekly saying...this is who we show in our system as reporting to you, is it correct? If not, make your changes to the attachment and send it back to us and we'll update our system.

To do this, I need the following to happen...

I have one master sheet that lists 12,000 employees. I need some automated process to break this one file into several files (a file for each manager), then attach that file to an email compsed to that specific manager.

There are about 1,400 managers, so there should be 1,400 emails going out every week. I do understand the Exchange nightmare this will cause but for now, it's our only option as we are not all on one network, so we can't share a file on the network.

ATTACHED IS A SAMPLE FILE

**mrice** · 05-29-2010, 01:44 PM

You might be better using Access 2007 which has some quite elegant functionality built into it to collect data by e-mail. Have a look at http://office.microsoft.com/en-us/ac...154271033.aspx.

There's also Infopath which allows the construction of customised forms.

**shane.albrandt** · 06-02-2010, 01:20 PM

Thanks for that information. I'm afraid I'm less savvy in Access than I am in Excel. Yikes! Is there any way to get this done using Excel?

**mrice** · 06-04-2010, 04:50 PM

Yes but it would be much more complex in Excel and prone to problems as spreadsheets are much more difficult to bulletproof than databases which are designed to be rigorous.

**foxguy** · 07-05-2010, 04:58 PM

Hi shane.albrandt;

I just discovered this thread in my subscriptions. Sorry for not answering earlier.

Don't know if you're still looking for an answer.
The thread isn't marked "Solved", but I don't want to waste your time if you're satisfied.

**snb** · 07-05-2010, 05:50 PM

Sub tst()
  Set ol = CreateObject("outlook.application")

  sq = Sheet1.UsedRange
  c01 = sq(2, 4)

  For j = 2 To UBound(sq)
    Do Until c01 <> sq(j, 4) Or j = UBound(sq)
      c02 = c02 & vbCr & sq(j, 2)
      j = j + 1
    Loop

    With ol.createitem(0)
      .To = c01
      .Subject = "employee check"
      .body = "Your employees: " & c02
      .Send
    End With

    c01 = sq(j, 4)
    c02 = sq(j, 2)
    Next
End Sub