none
VBA - Macro to open excel file & create email based on Excel cell contents RRS feed

  • Question

  • Hi again, and thank you for taking the time to check out my problem.

    I am still not confident using <acronym title="visual basic for applications">VBA</acronym> but have tried to amend code to enable me to get the functionality I am after, but I have failed.
    I'll try to break it down for easier reading (hopefully).

    The objective:
    Whenever a user clicks on a macro button on the Outlook toolbar, it will open a specific Excel file, whereby the user selects a number and an Item Description which then opens an Outlook email with the selected number and Item Description in the Subject field.

    3 stages:

    Stage 1:

    Macro button in Outlook opens Excel spreadsheet.

    Stage 2:

    A) User clicks on the next available number which automatically enters the user's ID/Name and the Date the Number is Taken (Date is to remain static so when another user opens the Excel file, the date the previous users recorded is not changed).

    B) User selects Item Description in Excel from dropdown list.

    C) When user is satisfied with the selection, the user clicks on Open email, which generates an Outlook email
    based on a template.

    Stage 3:

    The email opens with the selected number and Item Description in the Subject of the email.

    I do have a sample spreadsheet with layout email screen dump but that is of little use when it cant be uploaded So I'll try to generate the look of the spreadsheet in the table below ... with sample data.

    Incident # Taken By Date & Time Item Description Open Email
    Inc201411-0001 iim 20/10/14 This is from a drop down list FROG Open Email
    Inc201411-0002 bba 21/10/14 This is from a drop down list
    Dog
    Open Email



    I already have a spreadsheet with similar columns and functionality, (2 columns: INCnumber & User ID) which only
    generated an email after double clicking on the IncNumberand inserting the INCnumber in the email subject and recording the user's ID.

    I based my sample spreadsheet on the one I had, and just added 3 columns (Date & Time, Item Description and Email), because more functionality is required.

    The code for the similar spreadsheet is Under 'General' and Worksheet_BeforeDoubleClick
    ****************************************************************************************

    [Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    inc = Selection.Value
    ans = MsgBox("Is this incident number correct?" & vbNewLine & vbNewLine & "Incident Number: " &
    inc, vbYesNo, "Correct Incident Number?")
    If ans = vbNo Then
    Exit Sub
    Else
    End If
    If Selection.Offset(0, 1).Value = "" Then
    Selection.Offset(0, 1).Value = Environ("UserName")
    Else
    MsgBox "This incident number has already been assigned, please select again", , "Incident
    Already Assigned"
    Exit Sub
    End If
    Dim objOut As Outlook.Application
    Set objOut = CreateObject("Outlook.Application")
    'Note: Change the file path in the line below between the ""marks to the file path of where the
    Outlook template is store,
    'and include the name of the template
    Set obJMailItem = objOut.CreateItemFromTemplate("S:\SRC\Dispatch\Incident Reports\Incident
    report.oft") '#### REPLACE WITH THE FULL PATH TO YOUR TEMPLATE FILE #####
    'Note: the line below enables you to include text in the Subject field of the email. Enter in text
    ONLY between the "". You can edit the
    'subject field once the email is generated.
    obJMailItem.Subject = inc & " - " & "SAC brief SITE NAME eg STP - EVENT TYPE " '##### YOU WOULD
    NEED TO HARD CODE THIS PART #####
    obJMailItem.Display
    Set obJMailItem = Nothing
    Set objOut = Nothing
    End Sub **************************************************************************************
    The Outlook <acronym title="visual basic for applications">VBA</acronym> code(macro) module is located under General and basOpenIncidents:

    Sub basOpenIncidents()
    Dim objExcel As Object
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    objExcel.Workbooks.Open "S:\SRC\Dispatch\Incident Reports\Incident Number List and Report-NWK.xlsm"
    Set objExcel = Nothing
    End Sub



    Thanking you in advance for any assistance.

    I'm sure there will be details that you'll need clarification on since I can't upload the spreadsheet.

    Cheers
    ,
    ShyButterfly


    Hope you have a terrific day, theShyButterfly

    Thursday, October 30, 2014 11:11 AM

All replies

  • Hi ShyButterfly,

    >>The code for the similar spreadsheet is Under 'General' and Worksheet_BeforeDoubleClick <<

    What did 'General' mean? I am not able to understand exactly the issue you are handling.

    >>The Outlook <acronym title="visual basic for applications">VBA</acronym> code(macro) module is located under General and basOpenIncidents: <<

    Can you explain more detail about the code below used for?

    <acronym title="visual basic for applications">VBA</acronym> 
    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.

    Friday, October 31, 2014 7:10 AM
    Moderator
  • Hi Fei,

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

    What I meant by

    >>The code for the similar spreadsheet is Under 'General' and Worksheet_BeforeDoubleClick <<

    was that the code is located under the 'Worksheet' object and and the 'Procedure' being the 'BeforeDoubleClick'

    What I meant by .....

    >>The Outlook <acronym title="visual basic for applications">VBA</acronym> code(macro) module is located under General and basOpenIncidents: <<

    Sub basOpenIncidents()
    Dim objExcel As Object
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    objExcel.Workbooks.Open "S:\SRC\Dispatch\Incident Reports\Incident Number List and Report-NWK.xlsm"
    Set objExcel = Nothing
    End Sub
    the code above is in Outlook2010

    ..... the Object is General and the Procedure is basOpenIncidents -  that is the VBA Macro created in Outlook 2010 instructs Outlook to open the spreadsheet, so the user can make selection

    If the existing codes are creating more headaches in trying to tie it in with what I am asking assistance for, I don't mind if you want to start from scratch.

    I've taken a picture that will hopefully explain a bit better what I am trying to achieve. However, I don't know how to upload it as I don't own a website.

    Note: The spreadsheet will be 'shared' by a group of users ... so if a prompt could also be created so they will remember to Save the file as soon as they have selected the Inc number and the drop-down option has been selected.

    Sorry if I have confused you further, it's hard to describe without visual aids  ... if I could show you the picture it may shed some more understanding of what I mean.

    With much appreciation,

    ShyButterfly


    Hope you have a terrific day, theShyButterfly

    Friday, October 31, 2014 8:42 AM

  • Hi ShyButterfly,

    Thanks for the detail information for this issue.

    >>..... the Object is General and the Procedure is basOpenIncidents -  that is the VBA Macro created in Outlook 2010 instructs Outlook to open the spreadsheet, so the user can make selection

    If the existing codes are creating more headaches in trying to tie it in with what I am asking assistance for, I don't mind if you want to start from scratch. <<

    Did you mean that you want to let user select the content in Excel and send emails based on the contents of selection?

    If I understood correctly, we can use code below to achieve the goal:

    Sub basOpenIncidents()
    Dim objExcel As Object
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    objExcel.Workbooks.Open "S:\SRC\Dispatch\Incident Reports\Incident Number List and Report-NWK.xlsm"
    
    Set ret = objExcel.InputBox("please select the cells!", Type:=8)
    'get the values of selection via ret
    Set objExcel = Nothing
    End Sub

    >>Sorry if I have confused you further, it's hard to describe without visual aids  ... if I could show you the picture it may shed some more understanding of what I mean.<<

    As far as I know, the account need to be verified to upload pictures, if you have issues using this forum, I suggest you reopen a thread in Forums Issues (not product support) 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.

    Tuesday, November 4, 2014 6:50 AM
    Moderator
  • Hi Fei,

    I apologize for the delay in response (family issues).

    I appreciate what you have provided, however that doesn't  do what I was hoping it would do. It opens the spreadsheet, but that's all.

    I've attached the file to SkyDrive ..... with comments and screen dump of the end result.

    I hope this explains it a bit better :)

    Link to sample file

    <iframe frameborder="0" height="346" scrolling="no" src="https://onedrive.live.com/embed?cid=F403A6FC3C2C55BA&resid=F403A6FC3C2C55BA%21979&authkey=ABnZL0MrrmNPkGM&em=2" width="402"></iframe>Thank you for your time and consideration :)

    TheShyButterfly


    Hope you have a terrific day, theShyButterfly




    • Edited by theShyButterfly Sunday, November 23, 2014 9:10 AM Still getting 'Runtime Error' - tried another method
    Sunday, November 23, 2014 8:36 AM
  • Hi TheShyButterfly,

    >>I appreciate what you have provided, however that doesn't  do what I was hoping it would do. It opens the spreadsheet, but that's all.<<

    No, the code should dispaly a input box to let user select a specific range. And then you can get the value of ranges selected by users. Did you get any errors? Or Did I misunderstood the requirement?

    In addtion, the link to sample file is broken.

    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.

    Monday, November 24, 2014 8:51 AM
    Moderator
  • Hi Fei,

    If I can get the link to my file to work, you should be able to get a better idea of what I am trying to do/get done.

    Link to sample workbook

    Let me know if you need further information ... I believe that combined with the Objective I initially published ....

    "The objective:
    Whenever a user clicks on a macro button on the Outlook toolbar, it will open a specific Excel file, whereby the user selects a number and an Item Description which then opens an Outlook email with the selected number and Item Description in the Subject field.

    3 stages:

    Stage 1:

    Macro button in Outlook opens Excel spreadsheet.

    Stage 2:

    A) User clicks on the next available number which automatically enters the user's ID/Name and the Date the Number is Taken (Date is to remain static so when another user opens the Excel file, the date the previous users recorded is not changed).

    B) User selects Item Description in Excel from dropdown list.

    C) When user is satisfied with the selection, the user clicks on Open email, which generates an Outlook email
    based on a template.

    Stage 3:

    The email opens with the selected number and Item Description in the Subject of the email."

    Thank you again for your time

    Cheers,

    ShyButterfly


    Hope you have a terrific day, theShyButterfly

    Thursday, November 27, 2014 8:16 AM
  • Hi ShyButterfly,

    I am trying to understand the requirment.

    >>Macro button in Outlook opens Excel spreadsheet.<<

    After we write a macro in the Outlook, we can add the macro to the ribbon as a button as figure below:

    >>A) User clicks on the next available number which automatically enters the user's ID/Name and the Date the Number is Taken (Date is to remain static so when another user opens the Excel file, the date the previous users recorded is not changed).

    B) User selects Item Description in Excel from dropdown list.

    C) When user is satisfied with the selection, the user clicks on Open email, which generates an Outlook email
    based on a template.<<

    I am not able to understand the step a. What did you mean the avaiable number? If you want to generate the email based on the selection of users, here is a sample. It set the subject and mail body based on the cell selected by users:

    Sub MySub()
    Dim objExcel As Object
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    objExcel.Workbooks.Open "C:\Users\userName\Desktop\Book1.xlsx"
    
    Set ret = objExcel.InputBox("please select the cells!", Type:=8)
    'get the values of selection via ret
    
    Set objMail = Application.CreateItem(olMailItem)
     
     With objMail
     
     'Set body format to HTML
     
     .BodyFormat = olFormatHTML
     .Subject = ret.Value
     
     .HTMLBody = ret.Offset(0, 1).Value'the body is the next cell beside the cell selected
     
     .Display
     
     End With
    
    Set objExcel = Nothing
    
    End Sub
    

    I suggest that you separate the technique issue from the requirement to help us understand the issue exactly.

    Also here are the links for you learning Excel and Outlook Object model.

    Object model reference (Excel 2013 developer reference)

    Object model (Outlook 2013 developer reference)<o:p></o:p>

    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.

    Tuesday, December 2, 2014 8:44 AM
    Moderator