I’ll be doing some blog maintenance in the next few days.
The code highlighter blows, so I’m replacing it with one that can also be used in the comments. That means I have to go through all 75+ posts and change the code to display the VBA properly 
And I’m upgrading to Wordpress 2.6.2.
So the blog might be down periodically. When I’m updating, you might get a “back in 15 minutes” sign.
Apologies in advance.
–JP
Do you ever need to forward attachments to someone else (maybe your boss), but don't want the ugliness of "FW: FW: re:" in the subject line? Why let others choose how your emails are going to appear -- here's an amalgam of code I've posted previously (mainly from Processing multiple emails) that does the following: You select a series of emails (or even just one email) with attachments, and the code saves all the attachments to a folder on your desktop, then re-attaches all of them to a single new email to which you can craft a new subject, and send it wherever you want.
So when 10 people in your office forward you attachments in 10 different emails, and you need to forward all of the attachments on to the same place, this code can be used to combine them all into one email.
The first thing you'll need to do is select the message(s) with attachments that you want to combine into one email. The code sets an object reference to the selection and saves all of the attachments from each of them.
We use an encapsulated function called MyDesktopPath, which I found in a newsgroup posting. The Windows Script Host Object Model has a SpecialFolders Method which can return the path to the end users' Desktop folder. This will let us determine the correct folder without any special or complicated programming. We'll create a temp folder called "Saved Attachments" which will store all of the attachments from all of the emails we selected.
We'll use the Scripting.FileSystemObject to check if the folder already exists. Here's the function that returns an object reference. It's fully encapsulated so you can just drop it into any project you need and it returns a reference to that object.
Then we'll use a For Each Loop, setting an object reference to each mailitem in turn. There's a loop-within-a-loop, where we loop through the attachments collection for each mailitem and save them to that temp folder mentioned earlier.
In order to attach files to the newly created email from our desktop folder, a Do Loop is used to get successive filenames, and the Add Method of the Attachments Collection adds them to the message. At this point, you can do whatever you want with the email; I chose to use the Display Method to show the email, so I can type in the recipient(s), subject, etc. If you always send the attachments to the same place, you can pre-fill this information and use the Send Method instead.
Enjoy,
JP
As you might have read in my post Excel User Conference Update, I attended the Thursday session of the Excel User Conference. Due to work constraints (which I will discuss below), I was only able to attend one day of the conference. I return home exhausted, more learned than I expected, with a new appreciation of my own knowledge and others' experiences with Excel. I'm still exhausted but I'll do my best here to impart my experience as coherent as possible. There's no VBA code in this post, if you were looking for that you might want to keep moving. 
I will have some code posted in a few days, after I catch up on my sleep debt.
I arrived in Atlantic City around 11:30 pm on Wednesday and crashed in the hotel room. The Taj Mahal was an excellent venue choice. The room was pretty big (king size bed!) and I tried to get some work done before getting to bed. Lately I've been writing some beginner VB .NET programs, using the free Express Edition available for download, and a list of code samples I found while browsing.
But unfortunately it was late and I was tired from the road, so after a short while I just crashed. Driving for long periods really takes a lot out of you; there's gotta be a name for it ("road hypnosis" ??). I got up around 7 and went down to the conference area at 8:00 am.
First I met Damon Longworth who is the conference organizer and also a Microsoft MVP for Excel. Very nice guy and we chatted a bit before some others arrived. I also met Bob Umlas and Jon Peltier at that time; again, two very nice guys. Bob has a very good sense of humor, and Jon has a deep understanding of Excel, both of which were demonstrated throughout the day. Everybody looks exactly like their picture so it's easy to spot the more well-known attendees.
First up was Jon Peltier showing some charting integration with Excel and PowerPoint. He demonstrated various ways to paste charts into PP slides. There was also some VBA automation of PowerPoint to manipulate slide and chart objects. His computer was a bit slow, but I think it was because of the point he was demonstrating: when you paste in a chart as a workbook object, the entire workbook gets pasted into the slide (including potentially sensitive or proprietary worksheet data). Jon used Excel 2003 for his talk. As soon as I get a chance I want to look at the code samples.
After that was Bob Umlas, another Excel MVP, who made it obvious very quickly that he knew what he was doing. He moved very quickly from basic to advanced array formulas and gave some extremely useful examples. He used Excel 2007 which he navigated pretty easily. He was the only one to use 2007. Most of the other commenters briefly mentioned their experience with 2007, which wasn't very positive.
Bob had some copies of his book for sale, which he autographed for some people. After he was done, I bought the 2003 version of This isn't Excel, it's Magic! and I'll sneak some reading time in between my coursework and the VB .NET stuff.
The weather sucked (cloudy and then light rain) but it didn't matter since I didn't spend much time outside anyway. Anyway, you don't go to AC to go outside.
After lunch, Bernard Liengme, another "Microsoft Excel" MVP (inside joke: Bernard mentioned that in his books he has to write it as "Microsoft Excel", not "Excel", per agreement w/ MS), made his presentation about using Solver. I had no idea that Solver was written by a third party, but after hearing about it, it sounds totally believeable. Other stuff like the spell checker, etc were written by third parties and just grafted onto Excel.
I wasn't expecting to learn much from this class, but Bernard made it interesting and showed how to apply Solver to real world problems. And he gave some crucial tips for making Solver work correctly (you'll have to ask him!). Having never used Solver before, I feel like I could actually use it now (at least on some rudimentary optimization problems to start).
The spreadsheets he used in his presentation were very well designed and I will make a point of completely comping his worksheet design style when doing any presentations of my own in the future. 
Last was Damon Longworth, who presented information about Pivot Tables. His presentation made me realize I know more about PTs than I thought. He demonstrated how to extract data from other sources such as a MS Access database. There was a lot of Q&A about PTs, and most of the other attendees had built them programmatically. Damon reminded us that using the same pivot cache for multiple tables is more memory and space efficient. He also showed us how to stop Excel from referencing PT data using the GETPIVOTDATA formula, by using the toggle button on the PT toolbar. Like Jon's presentation showed, this could also be a leak to allow others to view your source data.
Unfortunately I didn't get to meet everyone who attended, due to the structure of the sessions, which ran concurrently. If anyone reading this attended the Thursday sessions above, please let me know. Meeting Jon and Bob and the other MVPs was a great experience, as well as the other attendees, who had varying levels of expertise and even offered some help to the presenters. By my count, there were about 30-40 people there, which surprised me, considering how many people actually need the training offered, and Atlantic City being an ideal location for having a professional conference (second only to my hometown IMO).
The last class ended around 4:30 and I packed up and left around 5 pm.
A synopsis of my experience at the Excel User Conference (which, by the way, is still going on today) follows. In no particular order:
As I alluded on Simon Murphy's blog, my company decided not to pay for my training. I want to stress to anyone out there reading this, if you have an opportunity to attend a user conference, meet MVPs, whatever, even at your own expense, DO IT. There is simply no substitute for training from real-world experts.
[Update 9-28-08]: Jon Peltier posted some photos from the conference. Check it out in his Excel User Conference Recap
--JP
I wrote a small add-in that lets you switch between worksheets easily. Just download the XLA file, place in your favorite directory, and go to Tools > Add-Ins and browse for the file.
When installed, a menu item on the Windows menu called "Sheet Picker" is added. A form with a list of all the available worksheets appears. Just pick one and click "Go To Sheet" to switch to that sheet. The "Exit" button shuts down the form. It's pretty primitive so let me know if there are any bugs.
See you at the Excel User Conference!
--JP
In Delay Sending Outlook Messages, Debra Dalgleish writes about deferring delivery times for Outlook messages:
You hit Send, then realize you forgot the attachment. Or you misspelled the recipient’s name. Or you used Reply All, when you only wanted the message to go to one person. None of these things have ever happened to me (well hardly ever), but you might have been less fortunate.
Outlook 2003 and 2007 have the ability to hold all sent messages for a pre-determined length of time. The link to MS Outlook 2007's instructions are in Debra's post.
If you set this option under "Rules And Alerts", it can apply all outgoing messages equally. You can also set this on a per-message basis by selection "Options" in an open email, and putting the date and time in the appropriate boxes.
There are good reasons for doing this, for example, if you respond to a certain email too quickly, it sets unreasonable expectations for future responses. You don't want to give your recipients (especially your boss) the appearance that you are sitting at your desk doing nothing; that's a sure way to get bombarded with emails. You've trained them to expect a fast response, so when you respond "normally" next time, to them it will appear slow. Let the misery begin!
You can also use this feature to manage your email and even make it appear that you are at your desk duly reading and responding to emails. You can craft pseudo-autoresponses that make it appear you've actually read the email! All by just deferring the delivery time. I call them "pseudo" because a real autoresponse comes in almost instantly (like Out Of Office), making it appear computer-generated, cold, and making you feel like your email is definitely unread. But by delaying the response, we can fool the recipient into thinking their email is actually being read.
We can defer emails programmatically in several ways:
I'll demonstrate the second way here. First you paste the following at the top of your ThisOutlookSession module, in the (Declarations) section:
Then we need to initialize the event variable above by placing the following code in the Startup event:
This will act as a monitor on the default Inbox. Here is the event code that will actually do the work when a new email is received. The ItemAdd event is triggered whenever a new item is received or placed in the default Inbox.
Whenever an email is received (a MailItem Object), we call the sub SendMsg, which is going to provide the response.
This sub takes three arguments:
Test it out and you will see the deferred delivery time is set to the same date as the email, with 10 minutes added to the delivery time.
We can get very clever with this as well. Instead of a set time of 10 minutes, we can make it random so our recipient can't tell it's a machine response. Change the ItemAdd Event as follows:
The Randomize function shuffles the deck, then we assign a random number between 1 and 10 to the Integer variable iTimeToSend. This will defer the delivery time of the email to a random time 1 to 10 minutes into the future. In other words, some replies will go out in 5 minutes, others in 7 minutes, 2 minutes, and so on. For full effect, make sure you use a conversational tone, and use the sender's first name (Left$(Msg.SenderName, InStr(1, Msg.SenderName, " ") - 1)) to personalize the response.
This can get as complicated as you want. You can set up string constants that represent different types of replies (the sMsg variable) and use Select Case statements to call the SendMsg function with different parameters depending on the sender, keywords in the subject, number of attachments, etc. Let your mind wander and I'd be glad to see the results. I'd love to see someone develop a fake AI that sends back responses like "That's interesting, tell me more!" and "Oh really?" to keep an email conversation going all on its own.
Enjoy,
JP
Add to Google Reader: 
Add to Yahoo!: 
 |  |  |  |  |  |  |  |  |  |  |  |
 |  |  |  |  |  |  |  |  |  |  | |
