Emailing; Find identity of sender

[jomili]

I've got a macro that will email a spreadsheet out to any of a dozen people. Each of these people will need to use this macro to send a copy of their spreadsheet to me and my boss, but not necessarily to everyone on their list. I know how to send it to me, and to my boss, but how does Excel know to send it also to the person who's sending it (I know they can go to their outbox to retrieve it, but for neatness sake I'd like them to get a copy in thier inbox). I'm using Outlook 2003 and Excel 2003.

[Leith Ross]

Hello jomili,

What you are describing is know as a "Blind Carbon Copy", abbreviate BCC. Set this property in the Outlook MailItem and assign your email and your boss's email to it as a semicolon separate string.

Example

  Set olApp = CreateObject("Outlook.Application")
  Set olEmail = olApp.CreateItem(0)
    With olEmail
      .To = "J.Smith@xfoo.com"
      .Subject = "Current assignments
      .Body = "The current job assignments are attached."
      .BCC = "You@wherever.com; Boss@wherever.com"
      .Send
    End With

[jomili]

Leith,

I'm sorry, my post wasn't clear enough.

The routine will be run by about a dozen people. Already it emails me and my boss. What I want is for the report to mail itself to the SENDER, whoever that sender is, rather than having them go to their "Sent Items" for a copy.

Since I don't know who will be sending, I can't hardcode their email address in.

[Leith Ross]

Hello jomili,

For example, I email the report to you. I should then in turn receive an email to myself of the email I just sent you? Is this report an attachment?

[jomili]

Yes.

The userform boils a lot of information out of various spreadsheets and puts it into a small report which tells my users what problems they need to look into.

It sends a copy of that report as an attachment to an email to me and to my boss, so we know that 1) the user DID run the report, and 2) they ran it at THIS time. The user as well needs to keep a copy of the email in case there are ever any questions. While they could retrieve it from their sent mail, I would much prefer that they instead also get copied on the email.

Possble?

[Leith Ross]

Hello jomili,

Yes, it is possible. The user will then have a copy in his or her inbox and a copy in the sent items folders. It has been awhile, but if I remember right, accessing the "sender" property will pop up another security dialog. I'll write up a macro and test it before I post back with it.

[broro183]

hi all,

It sounds like you're sorted with the help from Leith
I've previously used a Constants sheet within the file where the email strings were listed & then drawn into the macro using named ranges.


However, I thought I would throw this link in (http://www.rondebruin.nl/mail/folder3/signature.htm) as it may be of interest to people searching this thread.

hth
Rob