none
Excel VBA Userform – Copy Page 1 of Multipage and embed in Outlook email RRS feed

  • Question

  • Hi again,

    Thank you for taking the time to look into this.

     

    Objective:

    User completes page 1 of user form (PgNewIncident) and clicks the ‘Email’ command button, which opens MS Outlook and automatically embeds page 1 of the PgNewIncident (form).

    I would like the Subject to automatically extract the;

    • Incident # (txtSEC_INC_No),
    • Sac# (txt_SAC_No),
    • Site Address (txt_Site_Address),
    • And the Incident Category (cmbox_IncCategory).

    The recipients should be predefined as this will be static (To, CC)

    I don’t want it to automatically email itself, it needs to open so that if there are photo’s or other attachments that need to be included in the email, the user can then attach them before sending the email.

    I have researched sending an email from Userform, however, they are all referring to cell ranges, but I would like page 1 of the Userform.

    Is this possible … I’ve been trying to think logically and believe it would be since page 1 (multi page) is an object, but I have no idea how to make it work?

    I am also thinking that it would be efficient if I create a macro on excel whereby the userform is called up clicking on a button on the Excel Toolbar (rather than searching for the file).

    I’m asking about it now so that there is some time to contemplate whether it is possible or not, whilst I work on rectifying some glitches on my form.

    I’d be most interested to find out if this is possible and if someone may be able to help me out with the coding.

    Kindest regards,

    TheShyButterfly


    Hope you have a terrific day, theShyButterfly

    Tuesday, April 7, 2015 2:07 PM

Answers

  • If by "capture" you mean a screenshot it would be possible to copy the entire form (an API call) and paste as an image into the outlook form, would require a fair few API calls and testing. To copy just a page might also be possible but even less straightforward.

    It would probably be better to extract the data from your form and present appropriately in a mail item as you say. Before even trying to think outside the box, what is the objective of the email, what does the recipient want from it, what's the easiest for the recipient to digest and/or copy elsewhere.

    You can program, ie automate, Outlook and mail handling form Excel. In addition to the links Fei gave you Ron de Bruin has some great Outlook-from-Excel examples

    Wednesday, April 8, 2015 1:57 PM
    Moderator
  • Hi Ingelise,

    According to the description, you want to copy the one page of MultiPage control on the form into Outlook by click on the button on the form.

    Based on my understanding, it was not available to achieve since we can't embed a user form control in the mail body.

    As a workaround, we may insert the text into mail body based on the value you wanted on the page of MultiPage. To open Outlook and create an mail item and insert the body message, we can automate the Outlook via Outlook object model. And here is an helpful link for your reference:
    How to automate Outlook by using Visual Basic

    Also you can get more detail about MailItem object from link below:
    MailItem Object (Outlook)

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, April 8, 2015 9:03 AM
    Moderator
  • Hi TheShyButterfly,

    I agree with the opinion of Perter about mail message.

    >>But maybe you could advise whether it would be possible to base the email on a Outlook template form ... would it be possible to copy over the data from the textboxes in the Excel Userform to the textboxes created in an Outlook form? (one thing that just occurred to me as I read your response ... sorry, I just try to think outside the box LOL)<<

    If I understood correctly, you want to design a custom form for Outlook and set the control on the form based on the values from UserForm in Excel.

    Yes, it is possible. We can set the custom filed bound on the control on our custom form using MailItem.UserProperties and the following sample is that set the custom filed("MyField") after we create and display an mail item:

    Dim aMailItem As MailItem
    Set aMailItem = Application.ActiveInspector.CurrentItem
    aMailItem.UserProperties("MyField").Value = "Hello Word!"

    And if you still have issue about customize and automate Outlook application, I suggest that you reopen a new thread in Outlook forum to get more effective response.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, April 9, 2015 7:42 AM
    Moderator
  • For someone who says they are new to VBA you have managed to do a lot:)

    To embed a screenshot of the whole form would be possible but would take me a while to work out and test, and the multipage only probably a lot more. It would be much simpler to extract the data and include as html. More importantly I suspect your client and users would prefer that.

    Look at Ron's Mail Range/Selection in the body of the mail

    For your purposes, rather than copying some selection into a new sheet, make a template sheet in the same file as your form formatted as you like into which you can dump the data from your form. You could make it look like or give it a style similar to your form. When ready to send copy the sheet as a new workbook, do the publish stuff on the copied workbook, the fso stuff, clean up. 

    While testing Ron's example don't forget to change his address to yours in the demo code

    Friday, April 10, 2015 12:02 PM
    Moderator
  • Yep, you've got it!

    For sure there'll be plenty of testing to do. Couple of quick thoughts: with variable text lengths may want to format the data cells as wrap-text, and autofit rows before sending. 

    Probably worth deleting each data cell with each new session or form load. Data cells could be Named, they could be updated in textbox change events rather than only before sending. No doubt other things. 

    In a light test of Ron's demo I got a security warning about another app (my Excel) accessing Outlook. Not sure how best to address that but would be the same issue with your screenshot. No doubt there is a normal way of dealing with this.

    Sounds like you're enthused!

    PS about sheet code names
    The advantage of using codenames is they don't change if the user renames the sheet. Although possible it's rare a user will ever change the codename. The disadvantage of using codenames is to refer to a sheet directly it must be hardcoded, eg ThisWorkbook.Sheet1. That's fine if the sheet will always exist with the given codename, but if not the code will blow and and an error handler won't help. Also newly added sheets don't expose any codename until the VBE editor has been opened. The only way to refer to a sheet with a non hardcoded codename is to loop the collection (aircode)

    For each ws In Activeworkbook.Worksheets
        If ws.CodeName = "myCodeName" Then  ' got it

    Friday, April 10, 2015 3:20 PM
    Moderator

All replies

  • I'm not exactly sure what you want to do, but you should probably start here.

    http://www.rondebruin.nl/win/s1/outlook/bmail2.htm

    Maybe you can get acquainted with that, and post back with specific/additional questions.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, April 7, 2015 3:26 PM
  • Hi ryguy72,

    Thank you for taking the time to look at my query.

    Thank you for the link, but I have already looked at this, and as I mentioned in my query, this is referring to cells within the spreadsheet.

    I don't know how much more specific I can get than to say, I need to be able to 'capture' the page1 of the multi-page and have Outlook open with the page displayed in the body of the email (as per sample below, but just the first page) by clicking on the 'email' button within the form.

    I would presume, since page1 of the multipage is an object, that it would be possible to do this :)

    As I said, I have tried to research this, but all the codes etc are all referring to CELLS on a spreadsheet, or an actual spreadsheet ... so it's not much good to me.

    But perhaps you may know of someone/somewhere that may be able to help me .... surely I am not the only person out there that would find this advantageous.

    Thanking you again :)

    Cheers,

    Ingelise


    Hope you have a terrific day, theShyButterfly

    Wednesday, April 8, 2015 3:34 AM
  • Hi Ingelise,

    According to the description, you want to copy the one page of MultiPage control on the form into Outlook by click on the button on the form.

    Based on my understanding, it was not available to achieve since we can't embed a user form control in the mail body.

    As a workaround, we may insert the text into mail body based on the value you wanted on the page of MultiPage. To open Outlook and create an mail item and insert the body message, we can automate the Outlook via Outlook object model. And here is an helpful link for your reference:
    How to automate Outlook by using Visual Basic

    Also you can get more detail about MailItem object from link below:
    MailItem Object (Outlook)

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, April 8, 2015 9:03 AM
    Moderator
  • Hi Fei,

    Thank you for your prompt response :)

    It's disappointing that it can't be done ... and I will have a look at the links you provided (Thank you)

    But maybe you could advise whether it would be possible to base the email on a Outlook template form ... would it be possible to copy over the data from the textboxes in the Excel Userform to the textboxes created in an Outlook form? (one thing that just occurred to me as I read your response ... sorry, I just try to think outside the box LOL)

    So if I create a Outlook template form, set out as I wish, with the textboxes that I would like populated from the Excel Userform controls when the command to 'Email' is clicked, how/where would the code be programmed, in Outlook and/or Excel?

    Cheers,

    TheShyButterfly


    Hope you have a terrific day, theShyButterfly

    Wednesday, April 8, 2015 11:18 AM
  • If by "capture" you mean a screenshot it would be possible to copy the entire form (an API call) and paste as an image into the outlook form, would require a fair few API calls and testing. To copy just a page might also be possible but even less straightforward.

    It would probably be better to extract the data from your form and present appropriately in a mail item as you say. Before even trying to think outside the box, what is the objective of the email, what does the recipient want from it, what's the easiest for the recipient to digest and/or copy elsewhere.

    You can program, ie automate, Outlook and mail handling form Excel. In addition to the links Fei gave you Ron de Bruin has some great Outlook-from-Excel examples

    Wednesday, April 8, 2015 1:57 PM
    Moderator
  • Hi TheShyButterfly,

    I agree with the opinion of Perter about mail message.

    >>But maybe you could advise whether it would be possible to base the email on a Outlook template form ... would it be possible to copy over the data from the textboxes in the Excel Userform to the textboxes created in an Outlook form? (one thing that just occurred to me as I read your response ... sorry, I just try to think outside the box LOL)<<

    If I understood correctly, you want to design a custom form for Outlook and set the control on the form based on the values from UserForm in Excel.

    Yes, it is possible. We can set the custom filed bound on the control on our custom form using MailItem.UserProperties and the following sample is that set the custom filed("MyField") after we create and display an mail item:

    Dim aMailItem As MailItem
    Set aMailItem = Application.ActiveInspector.CurrentItem
    aMailItem.UserProperties("MyField").Value = "Hello Word!"

    And if you still have issue about customize and automate Outlook application, I suggest that you reopen a new thread in Outlook forum to get more effective response.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, April 9, 2015 7:42 AM
    Moderator
  • Thank you Peter and Fei for your responses :) I really appreciate your suggestions.

    Just a little explanation of why I am seeking a solution in the way that I have requested it.

    The reason why a copy/snapshot, or form duplication of the 1 multipage and activating Outlook from a call procedure in the Userform is:

    • notification of the instance of an incident having occurred
    • easier to monitor a mailbox than to keep looking in a file to see if there has been an incident that has to be followed up
    • that all the relevant and basic information is there for managers and others to easily see
    • recipients can forward the email with all the details, quickly and can attach any photos or other files related to the incident
    • the user has only to complete 1 form and not waste time duplicating information

    I am also trying to be considerate of the face that I am not a VBA guru (only a beginner) so trying to find the 'simplest' and fastest way to getting a solution.

    I have had a look at all the links you've provided and the majority of them I have already had a look at, but sort of dismissed them because my skills are not adequate enough at this stage to adapt them into something that will work. I also try to consider that any help I receive is given voluntarily by such brilliant people as yourselves and can not/would not expect you to create my project for me.

    I know it sounds crazy, but I am leaning more towards the 'snapshot'/screenshot of the form, but that is said without understanding the complexities ... because having to 'duplicate' the form in Outlook, to draw the contents of the userform controls into the body of the email automatically, sounds very complicated too.

    I would appreciate your opinion as to what you believe would be the best option for me, given my lack lack of VBA experience ... I'm willing to give it a serious go ... but don't want to waste your time either, as I'd expect I'd be asking for help along the way.  (not that it matters I guess, but I am no spring chicken LOL) ...but I'm not opposed to exercising my brain :)

    I look forward to your opinions.

    Thank you very much for your time and suggestions ... they are much appreciated.

    Cheers,

    TheShyButterfly (Grasshopper)


    Hope you have a terrific day, theShyButterfly

    Friday, April 10, 2015 7:50 AM
  • For someone who says they are new to VBA you have managed to do a lot:)

    To embed a screenshot of the whole form would be possible but would take me a while to work out and test, and the multipage only probably a lot more. It would be much simpler to extract the data and include as html. More importantly I suspect your client and users would prefer that.

    Look at Ron's Mail Range/Selection in the body of the mail

    For your purposes, rather than copying some selection into a new sheet, make a template sheet in the same file as your form formatted as you like into which you can dump the data from your form. You could make it look like or give it a style similar to your form. When ready to send copy the sheet as a new workbook, do the publish stuff on the copied workbook, the fso stuff, clean up. 

    While testing Ron's example don't forget to change his address to yours in the demo code

    Friday, April 10, 2015 12:02 PM
    Moderator
  • Ahhaarrr... :) Brilliant idea :)

    So, what I think you are saying is...

    Use my 'Userform' for the operator/user to complete, but have a spreadsheet within the same file set up just like my form, and when they click the 'Email' button, it will suck up the information from the spreadsheet layout/form?

    Excellent work around :) I think that's do-able for me :) .... may take a couple of days for me to set it up & trial it ... and knowing how things don't always go smoothly (LOL) .. I'm sure there will be a question popping up with which I may need some direction or help.

    Thank you for your vote of confidence :) ... I still have problems with "who do you call" when addressing sheets, workbooks, and other objects lol ... but I'm learning.

    Just one quickie though, that people don't seem to grasp ... from my research it appears logic to refer Worksheets by their 'code' name rather than their 'sheet name'... but I seem to have a really impossible time getting it to work (example Set = worksheet.wsIncident_Details, as opposed to Set=worksheet("Incident Details") ... is it not the correct way of referring to the worksheet code name?

    I'm excited now to progress to the next stage  :)

    Thank you SO much


    Hope you have a terrific day, theShyButterfly

    Friday, April 10, 2015 12:44 PM
  • Yep, you've got it!

    For sure there'll be plenty of testing to do. Couple of quick thoughts: with variable text lengths may want to format the data cells as wrap-text, and autofit rows before sending. 

    Probably worth deleting each data cell with each new session or form load. Data cells could be Named, they could be updated in textbox change events rather than only before sending. No doubt other things. 

    In a light test of Ron's demo I got a security warning about another app (my Excel) accessing Outlook. Not sure how best to address that but would be the same issue with your screenshot. No doubt there is a normal way of dealing with this.

    Sounds like you're enthused!

    PS about sheet code names
    The advantage of using codenames is they don't change if the user renames the sheet. Although possible it's rare a user will ever change the codename. The disadvantage of using codenames is to refer to a sheet directly it must be hardcoded, eg ThisWorkbook.Sheet1. That's fine if the sheet will always exist with the given codename, but if not the code will blow and and an error handler won't help. Also newly added sheets don't expose any codename until the VBE editor has been opened. The only way to refer to a sheet with a non hardcoded codename is to loop the collection (aircode)

    For each ws In Activeworkbook.Worksheets
        If ws.CodeName = "myCodeName" Then  ' got it

    Friday, April 10, 2015 3:20 PM
    Moderator
  • Yeah, glad you got it working.  Here's a couple of ideas about how to deal with the security popups.

    http://www.rondebruin.nl/win/s1/security.htm

    I've used clickYes before.  It worked great.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, April 10, 2015 4:24 PM
  • @ryguy72,
    Thanks for the heads-up. Looks like the Outlook prompt is setup specific and probably in the user's system/AV no problem to trust Excel. Curiosity which worked for you?

    Friday, April 10, 2015 7:06 PM
    Moderator
  • Here's an old post from a while back.

    https://social.msdn.microsoft.com/Forums/office/en-US/64aa3ef0-10ea-4987-9476-21d7c4e87d20/suppress-outlook-security-message-via-excel-vba-macro?forum=outlookdev

    This is a good read.

    https://www.outlook-apps.com/disable-outlook-security-warning/

    This is what I've used in the past.

    http://www.contextmagic.com/express-clickyes/


    I haven't had a need to do this in a long time, so I don't really know if any of the functionality has changed recently. 


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, April 10, 2015 8:02 PM