Consulting

Results 1 to 17 of 17

Thread: userform to choose sheets and send them by email

  1. #1

    userform to choose sheets and send them by email

    hi humans
    allo earth i need help
    i have 6 sheets in my example "acceuil" "hanifatoys" "aternal" "arba" "finale" "bl" and others...
    i want a userform like photo or another whatever from "acceuil" let me choice what of 5 others sheets saves in pdf and send as attachement by email
    userform.jpg
    one file pdf contain all the choiced sheets
    the email is extracted from VLOOKUP(D4,TABLO_CLIENT,7,FALSE) and also can be edited
    all the choices are on by default
    sorry if my request was made but otherwise than what I want and also for my english

  2. #2
    i give an example
    Attached Files Attached Files

  3. #3
    The attached should do what you want (based on the information provided). However Office does not provide the ability to collate sheets in a single PDF, so the attached creates a separate PDF for each selected sheet.
    Attached Files Attached Files
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,961
    Location
    Excel can create a pdf with multiple worksheets. The key is to put the worksheet tab names into an array. Order of the worksheets in the array does not matter. Worksheets are put into the pdf by their worksheet index order. When I want to do a specific order, I make the macro create a scratch workbook with worksheets in the order for the pdf.

    Simple example:
    'http://www.vbaexpress.com/forum/showthread.php?63325-Combine-2-sheets-into-1-PDF-file
    Sub Main()
      Dim s$
      Worksheets(Array("PrintCustomer", "Items")).Select
      s = PublishToPDF( _
        ThisWorkbook.Path, _
        ActiveSheet, _
        True)
      'Open file, remove or comment next line. This is just to show that it worked.
      Shell "cmd /c " & """" & s & """", vbNormalFocus
      Worksheets(1).Select
    End Sub
    
    
    Function PublishToPDF(fName As String, o As Object, _
      Optional tfGetFilename As Boolean = False) As String
      Dim rc As Variant
      rc = fName
      If tfGetFilename Then
        rc = Application.GetSaveAsFilename(fName, "PDF (*.pdf), *.pdf", 1, "Publish to PDF")
        If rc = "" Then Exit Function
      End If
      
      o.ExportAsFixedFormat Type:=xlTypePDF, Filename:=rc _
      , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
      :=False, OpenAfterPublish:=False
      
      PublishToPDF = rc
    End Function

  5. #5
    Yes that works . I wasn't aware Excel could do that. Every day is a school day. Thanks for the heads up.
    I have modified the attached to include your function and now only one PDF is created and attached.
    Attached Files Attached Files
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  6. #6
    thank you very much guys
    I did not answer GMAYOR yesterday I was trying to adapt your solution to my real file
    but roughly it works
    I try to remove the combobox and put the textbox since the choice of the customer and his email is already made
    and also add a button just for the creation of the PDF and that it is referenced in a table
    in any case thank you very much

    can we make the pdf unmodifiable ,only to read or print ? without password

    if I would like to exit the userform by the exit button at the top right and not by the CANCEL button I have a runtime error '-2147418105 (80010007)': Automation error

  7. #7
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,961
    Location
    If you modified the code and want troubleshooting help, attach the modified workbook. Graham can troublesheet the issue based on his code. It looks like the line with If .Tag = 0 in the main routine might be your issue. With your modified code that replaced his combobox with a textbox, you likely don't need a line like that.

    For the pdf modifications, you probably need to use a 3rd party application to flatten and or password protect the file. Several have GUI's to make that easy. Some have command line options that can do it. e.g. pdftk
    Of course with the command line switch features that some have, we can automate that in VBA using Shell().

  8. #8
    sorry i tore my hair out for no result
    I could not understand the macro
    I send my example
    please add comments in the lines
    I left the listbox just for the form
    Attached Files Attached Files

  9. #9
    Quote Originally Posted by chingilou View Post
    if I would like to exit the userform by the exit button at the top right and not by the CANCEL button I have a runtime error '-2147418105 (80010007)': Automation error
    In order to use the exit button (I invariably disable this in my own projects) add the following code to the userform.

    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
        If CloseMode = vbFormControlMenu Then
            CommandButton2_Click 'cancel button
            Cancel = True
        End If
    lbl_Exit:
        Exit Sub
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  10. #10
    thanks another times
    #9 work fine thanks
    please mr adapt your first macro to my file # 8 with comments and removing the listbox
    and update the sheets concerned before create pdf
    I can't do it
    Last edited by chingilou; 04-25-2021 at 12:26 PM.

  11. #11
    will it be abused if I ask you to send email with attachment from yahoo not from outlook
    thank you in advance

  12. #12
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,961
    Location
    This forum is about Office integration. This site has a subforum just for Excel VBA. http://www.vbaexpress.com/forum/foru...?17-Excel-Help

    In any case, CDO might be your answer for yahoo. Obviously, you need to change the inputs sent to the called Sub. I used early binding so you would need to set the CDO library reference as I commented.

    ' Add CDO reference for early binding method
    '  Tools > References > Microsoft CDO for Windows 2000 Library
    '    c:\windows\system32\cdosys.dll
    ' Log into Yahoo, Account Security, Set an APP password, use it below.
    
    
    Sub Test_eYahoo()
      eYahoo "YahooKen", "AppPasswordHere", "YahooKen@yahoo.com", "ken@gmail.com", "Subject", "Body", "D:\t\f.txt"
    End Sub
    
    
    Sub eYahoo(sUsr As String, sPass As String, sendFrom As String, _
      sTo As String, sSubject As String, _
      sBody As String, _
      Optional sAttachment As String = "")
      
      Dim cdomsg As New CDO.Message  'early binding method
      'set cdomsg=new CDO.Message 'early binding only
      'Dim cdomsg As Object 'late binding method
      Set cdomsg = CreateObject("CDO.message")  'late binding method or early binding
      
      cdomsg.Configuration.Load -1
      With cdomsg.Configuration.Fields
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        'Enter the username and password of your email account below
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = sUsr
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = sPass
        'Edit the SMTP server below e.g. smtp.gmail.com or smtp.mail.yahoo.co.uk
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.mail.yahoo.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465  '25, 465, 587
        cdomsg.Configuration.Fields.Update
      End With
      ' build email parts
      With cdomsg
        .To = sTo
        .From = sendFrom
        .subject = sSubject
        .textBody = sBody
        '.BCC
        '.CC
        '.ReplyTo = sendFrom
        '.HTMLBody
        '.HTMLBodyPart
        If Dir(sAttachment) <> "" Then .AddAttachment (sAttachment)
        .Send
      End With
      Set cdomsg = Nothing
    End Sub

  13. #13
    guys I thank you very much for the trouble you have done for me but I am exceeded I am a serious novice
    even with the comments I barely get there so without ,please make comments and make my file work as it should
    2021-04-26_230636.jpg
    Attached Files Attached Files

  14. #14
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,961
    Location
    At some point, you have to take ownership of the code. There is a language barrier here so I don't get what all you are pointing to.

    We can not guess at what your email username or password would be or some other things. Put the code in #12 into a Module. All you have to do is what I detailed. It is essentially a one line macro that you have to edit. It does not get much more simple than that.

    If you have not learned how to do early binding, I recommend doing so. That is what makes intellisense work for the user. Many coders will only use late binding for the final product or when helping others learn. They do this because some new coders have a hard time learning how to set a reference to a library object though it is very simple.

    eYahoo "YahooKen", "AppPasswordFromYahooAccountSecurity", "YahooKen@yahoo.com", "ken@gmail.com", "Subject", "Body", "D:\t\f.txt"
    You can look at the input parameter names for the Sub and see what those values mean. YahooKen would be "your" username. AppPasswordHere is the value that get from Yahoo in the Account Security as I explained. YahooKen@yahoo.com is the from email address. Put whatever but if they reply, it goes to your yahoo email address anyway. ken@gmail.com is the email to send TO. Obviously, use your own data values for testing purposes. The last 3 inputs are self evident.
    Sub eYahoo(sUsr As String, sPass As String, sendFrom As String, _  sTo As String, sSubject As String, _
      sBody As String, _
      Optional sAttachment As String = "")
    So, say the activesheet's, K1 cell has the lookup formula for the TO email address. Then one changes "ken@gmail.com" to: [K1]

    All of your other data, like building a subject or body string, would likely be some sort of string concatenation.

    Once you can do a basic yahoo email, we could maybe help a bit more.

    For what it is worth:
    Few people reply to a multiple task project/thread. This forum by nature may include multiple steps. You are better off getting help for a project if you can break it down to steps for separate threads. This also makes searching the forum for a solution by subject line keywords much easier. e.g.

    1. Make one pdf from multiple Excel userform selected sheets.
    a. Graham showed you how to dynamically build the array of sheet names.

    2. Iterate all cells in a data validation list.
    a. I have done this sort of thing to make payslip pdfs for each person.
    b. I think that you understand this one?

    3. Email using Yahoo, not Outlook.
    a. It is important to note limitations for solution methods as most would use Outlook for such a common thing.
    b. Ron de Bruin has many Outlook solutions, and some CDO solutions for: Outlook, gmail, and yahoo.

    In your use of yahoo, take note that they limit you to 100 emails per day using the App password. This is to limit scammers and junk mail senders.

  15. #15
    mr Kenneth Hobs I had a little guess for yahooken ect ..
    my 2 big problems and not the only ones
    1-creation of the multi pdf (I abandoned the idea to a single pdf and it suits my clients) I did not understand the graham routine to adapt it with my textboxs
    2-how to inform the macro that you gave me today all the pdf of the directory xxx
    can you give me an operational file
    my english is super poor I use the google translate I hope the general idea has come through

  16. #16
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,961
    Location
    More than 1 file attachment would require more work. First off though, you need to get the yahoo email to work. Post back when you get that working. Manually set an existing file attachment path like I did for that last input parameter value, "D:\t\f.txt". Doing many attachments like this complicates my #12 solution but is no real challenge. The challenge is figuring out what "you" want to attach. I guess that I would put the paths into an array and iterate that.

    For (1), Graham built the array dynamically from the selections in the userform. In #4, I showed how to manually create the array and he took it from there.

    Another method for (1) is to select the sheets as a group. This is how you do this method manually: hold ctrl key down, and click the sheets to select them for the group. Select File > Export > PDF, etc. And there you go, a multiple worksheet pdf. Graham, this is where we would Worksheets(1).Select True, and then if others, use False. It is a simple method. I use a boolean tf variable to know if I need to set True or False. One does need to deselect the group (select another sheet) at the end of pdf creation.

    Before I show you how to do (2), do as I said and try just making #12 work. If that does not work for you, adding the multiple attachments feature won't either. Of course adding all pdf files from a folder is different than anything discussed before. I guess one could but that is more code to handle cases of no pdfs and a loop for multiple .addattachement lines.

    This is how I solve problems, one at a time. I make some basic concept work, then I build on that. I also tend to code in modular fashion as you saw in #12. I can then reuse those routines very easily to help others. I most always comment in my macros where I posted a solution or tips for the solution that I create or modify. IF I showed you now how "I" get all file paths in a folder, your head might explode. You are confused enough I think.

  17. #17
    Quote Originally Posted by Kenneth Hobs View Post
    More than 1 file attachment would require more work. First off though, you need to get the yahoo email to work. Post back when you get that working. Manually set an existing file attachment path like I did for that last input parameter value, "D:\t\f.txt". Doing many attachments like this complicates my #12 solution but is no real challenge. The challenge is figuring out what "you" want to attach. I guess that I would put the paths into an array and iterate that.
    well i will start tomorrow with a predefined file
    Quote Originally Posted by Kenneth Hobs View Post
    For (1), Graham built the array dynamically from the selections in the userform. In #4, I showed how to manually create the array and he took it from there.
    I lack document to understand all the functions done by graham and you
    I'm a little used to uvba but never userform I know nothing that's why I swim blindly
    Quote Originally Posted by Kenneth Hobs View Post
    Another method for (1) is to select the sheets as a group. This is how you do this method manually: hold ctrl key down, and click the sheets to select them for the group. Select File > Export > PDF, etc. And there you go, a multiple worksheet pdf. Graham, this is where we would Worksheets(1).Select True, and then if others, use False. It is a simple method. I use a boolean tf variable to know if I need to set True or False. One does need to deselect the group (select another sheet) at the end of pdf creation.
    manually I know how to do it, it is by vba that I do not know
    in the same way the first graham file does it elsewhere
    by modifying spath and deleting kill ...
    but I would like to isolated this part for the create pdf only command button

    Quote Originally Posted by Kenneth Hobs View Post
    Before I show you how to do (2), do as I said and try just making #12 work. If that does not work for you, adding the multiple attachments feature won't either. Of course adding all pdf files from a folder is different than anything discussed before. I guess one could but that is more code to handle cases of no pdfs and a loop for multiple .addattachement lines.
    watch graham's first file
    if I chose the 5 I would have 5 pdfs files as the photo shows on the right.
    and I have the 5 attachments for outlook

    the whole solution of the problem the first graham file does it but this is the way I would like to change
    textboxes instead of listboxes and if possible yahoo instead of outlook
    and a command button in the middle of the step for creating only pdfs without sending

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •