Search code examples
vbaoutlookoutlook-2007

Embed form in outlook email and capture response

I am trying to help people do their job. So every morning an email needs to go out to the users with a list to Approve/Reject... Don't want to send this email just like a warning, telling them to go to the app to perform their duty. The email needs to have the capability to capture their response and update the DB accordingly.

option 1: Embed all items in single email body as a form, something like...

Code - Description  - radiobutton - radio button 

111  ABC  o Accept  o Reject
222  DEF  o Accept  o Reject

Submit

On submit need to call a winforms app and need to pass the code and accept/reject status. I have not been able to find any thing to come up with a solution. Need to validate as well, making sure all appropriate radio buttons have been selected. Is it even possible?

Option 2: I am trying to avoid voting route where each item will go as a separate email. when the user will accept/reject, a reply will go to the email. Then some process will monitor that inbox and will pick the not read messages and update DB accordingly. Using the MailItemClass in Microsoft.Office.Interop.Outlook namespace.

var mailItem = (Outlook.MailItem)Application.CreateItem(Outlook.OlItemType.olMailItem);
mailItem.To = "[email protected]";
mailItem.Subject = "This is a test";
mailItem.Body = "This is the body of the message.";
mailItem.VotingOptions = "Accept; Reject;";
mailItem.Send();
Solution

  • I believe what you are asking for is possible but would be a lot of effort to implement.

    For your approach, I assume the information for these emails comes from the DB. I would write a macro to interrogate the DB and create and send each email to the appropriate user.

    On each user’s system I would have a rule that recognised these emails and called a macro to process them. The macro would extract the contents of the email, copy it to a form and pass control to the user. A Submit button would validate the user’s choices and save the results in global variables before returning control to the macro. On regaining control, the macro would update the DB.

    This macro will be large and I am sure it will need maintenance. To protect itself from malware, Outlook does not provide any easy way of importing macros. The macro and the form will have to be manually imported to each user’s system, each time they change. Unless you have technically able users, it will be you who visits each system and updates it.

    I would reject any approach that required code on the user’s systems.

    To test an alternative approach, I manually created this email:

    Original email

    and sent it to myself. When I received the email, I actioned it as instructed. When I received the reply, it looked like:

    Reply to original email

    The Html body for this reply is full of formatting information. You could extract the responses from it but I believe the text body would be easier. To create the following, I started with the text body, replaced every carriage return and line feed with [CR] and [LF] and then added a few new lines to make it marginally easier to read. 

    [CR][LF][CR][LF] [CR][LF][CR][LF]From: Tony Dallimore [mailto:[email protected]] [CR][LF]Sent: 25 May 2018 14:22[CR][LF]To: 'Tony Dallimore' <[email protected]>[CR][LF]Subject: Test email 2[CR][LF][CR][LF] [CR][LF][CR][LF]
Please:[CR][LF][CR][LF] [CR][LF][CR][LF]
1)      Click Reply within the Home tab.[CR][LF][CR][LF]
2)      You may find it helpful to click Pop Out.[CR][LF][CR][LF]
3)      Enter “A” in the Accept column or “R” in the Reject column of every row in the following table.[CR][LF][CR][LF]
4)      Click Send.[CR][LF][CR][LF] [CR][LF][CR][LF]Code[CR][LF][CR][LF]Description[CR][LF][CR][LF]Accept[CR][LF][CR][LF]Reject[CR][LF][CR][LF]
111[CR][LF][CR][LF]ABC[CR][LF][CR][LF]A[CR][LF][CR][LF][CR][LF][CR][LF]
222[CR][LF][CR][LF]DEF[CR][LF][CR][LF][CR][LF][CR][LF]R[CR][LF][CR][LF]
333[CR][LF][CR][LF]GHI[CR][LF][CR][LF]A[CR][LF][CR][LF][CR][LF][CR][LF]
444[CR][LF][CR][LF]JKL[CR][LF][CR][LF]A[CR][LF][CR][LF] [CR][LF][CR][LF]
 [CR][LF][CR][LF] [CR][LF]

    You would need a second macro to process these emails, extract the responses and update the DB. There is no validation on the user’s system but they are not being asked to do anything complicated. If you telephone and harangue them every time you get an incomplete reply, they will soon realise life is more pleasant if they get it right first time.

    I do not know how difficult it is to access your DB from VBA but I do not believe anything else is particularly difficult.

    This is not the approach you requested but I believe this will be very much easier to implement. More importantly, all the code is on your system which will make development and maintenance so much easier.