Help with generating auto email and calendar macro

[cassie4258]

I need to do several things with this spreadsheet, and I'm not nearly familiar enough with the coding part to figure it out. Help will be much appreciated... especially with the email portion.

1.) I made a calendar macro (smiley face at top of spreadsheet) to fill in the "date" cells, but I would like it to pop up automatically when someone clicks in one of those boxes.

2.) I need the "number of days past due" to automatically calculate as a running talley when set to "past due" status



3.) The "tip" note doesn't pop up for every date blank like the "employee" one does, just the first cell at the top of column does.

4.) For the email portion, I would like Excel to send an email to the selected employees reminding them when the "status" flips to "past due"... and then at a regular interval afterwards, say once a week. Also wondering if there is a way to copy a supervisor on every past due reminder that is sent.

Thank you all in advance!

[BobBridges]

Ah, you did post (repost) on the programming forum. Ok, cassie, you say you're "not familiar enough" with coding to figure this out by yourself; I infer that you're at least a little familiar with coding, just not enough. How much do you know already? Do you just need a few pointers on the Outlook statements, and the rest you can do?

Outlook is a very different animal from Excel and I don't know the Outlook object model very well, but I have done some simple emails of the sort you're talking about; when I need to do it again, I just fetch existing code so that I can figure out how to do it again. Here's a snippet of code I just dug up from a previous .xlsm:

  ' Create the new email object
  Set ool = CreateObject("Outlook.Application")
  Set eml = ool.CreateItem(0) 'olMailItem
'  eml.Display
  eml.BodyFormat = 1 'olFormatPlain
  eml.To = EmlTo
  eml.Subject = "Manager " & va & "s for dept " & Dept & " (" & vcc & ")"
  eml.body = vm
'  eml.GetInspector.Activate
  eml.Send

The first statement sets up the Outlook Application object; from there you can do everything else in Outlook, just as in Excel everything starts with the Excel Application object. Notice that Office finds nothing difficult about opening Outlook inside a VBA/Excel program.

The CreateItem method starts a new object—the '0' type indicates that it's to be an email.

I commented out the Display method for this project, but you should enable it to start with; it makes the email window visible, which you definitely want during testing. Once your code works you can decide for yourself whether to comment out this statement.

Then I set BodyFormat to 1, plain text rather than HTML. You can do HTML too, but handling HTML is much more complicated and for ordinary boilerplate notifications it's not necessary. If you decide you want to enable HTML, you can, but get the email working first and then add the complication later if you still want to.

To, Subject and Body are text properties; you set them to one or more email addresses, the subject line and the text of your email, respectively. If you make the email window visible with the Display method, and then step through the program one statement at a time, you'll see those fields populated in the email window as the statements execute.

I forget what activating the GetInspector accomplished, and why I disabled it. Inspectors are very important in Outlook, but I don't think I've really understood them yet.

The Send method is obvious. But in your testing, don't do it at first. Here's the sequence I'd use:

1) Put my own email address in the To list at first, and comment out the Send statement. Run the program and look at the email it sets up. Correct errors. (There are always errors.)

2) Send the email manually (to yourself) and be sure you're still happy with it.

3) Enable the Send statement and let the program send the email to you without your looking at it first. Make sure you're still happy with it.

4) If you have to send the email to more than one person, set up the multiple To (and possibly CC and BCC) fields with multiple email addresses, using your own email address and maybe one or two willing guinea pigs; make sure you have that part working right.

Then you can do it for real—after warning your victims that the first email or two are test.

---

Two last notes:

1) If you're not far enough along in VBA to understand the above, no problem; we'll just back up and start slower, so you can understand what you're doing. Just ask questions.

2) Don't forget to look at Microsoft's documentation on the Outlook object model. You won't understand it at first—at least I don't—but I'm learning, and it's good to make a start, because it's a cinch that if you need to do this once, you'll need it again. You don't need to understand it all to do something, but you do need to understand a little more each time you do it again.