userform to choose sheets and send them by email

[chingilou]

hi humans
allo earth i need help :hi:
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
28347
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

[chingilou]

i give an example

[gmayor]

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.

[Kenneth Hobs]

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

[gmayor]

Yes that works :yes. 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.

[chingilou]

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

[Kenneth Hobs]

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().

[chingilou]

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

[gmayor]

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

[chingilou]

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 :crying::crying::crying:

[chingilou]

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

[Kenneth Hobs]

This forum is about Office integration. This site has a subforum just for Excel VBA. http://www.vbaexpress.com/forum/forumdisplay.php?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

[chingilou]

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
28371

[Kenneth Hobs]

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.

[chingilou]

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

[Kenneth Hobs]

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.

[chingilou]

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

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

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


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