Email completed PDF

I just emailed myself a pdf with the code. When I received it it showed it as a pdf but tried to open in notepad. Any thoughts? It would not recognize it when I tried to open with Adobe.
 
I'm using v1.2.3. It stores the file in the folder as a pdf that can be opened. When it emails it out is when it is having issue.
 
I'm seeing that here too... that is really weird. If the file is created fine, it should attach fine... I wonder what is going on here...

I may not be able to get you an answer on this one tonight, Randall. It's almost 11pm my time, and I've got to work in the morning. I'lll try a few tests on my side and get back to you though.
 
I understand Ken. Thanks again! This forum is a huge resource.
 
Got it.

So as it turns out, we test if the file shows up in Windows Explorer. What I didn't realize is that the code runs so fast that the file shows up, but the details aren't completely written to it before it gets uploaded to the outlook email. So basically you have a process that looks like this:
  • Routine triggers write
  • File created
  • Writing starts
  • File uploaded to outlook
  • Writing finishes
This explains why the file on the desktop opened... it was complete. But the outlook one was uploaded too quickly.

I've inserted a 1 second pause in the code to fix that issue. (It's the Application.Wait line). If you're still getting the error, try changing that to a 2 or 3 second delay instead.

Let me know if that fixes it up for you. :)
 

Attachments

  • Print and Email PDF v1.4.xlsm
    66.4 KB · Views: 26
I had to expand the time window to 3 seconds. Is there anyway to keep selected sheets from coming up in the userform as an option to print?
 
Of course!

Change the Userform's Initialize event to this:

Code:
Private Sub UserForm_Initialize()
    TextBox1.Value = "C:\Users\" & Environ("Username") & "\Desktop\test pdf"
    Dim ws As Worksheet
    For Each ws In Application.ActiveWorkbook.Sheets
        With ws
            If .Visible = False Then
                'Sheet not visible, so ignore it
            Else
                'Choose which sheets to ignore
                Select Case .Name
                    Case Is = "HideMe", "DontShow"  '<-- list the sheets you want to ignore
                        'Don't want to show the above, so ignore them
                    Case Else
                        Me.lstAvailable.AddItem .Name
                End Select
            End If
        End With
    Next ws
End Sub

You'd need to change the "HideMe", "DontShow" to the names of the worksheets you want to hide.

I also changed the code so that it defaults to the active user's desktop folder, rather than the hard coded one.

With regards to the pause, I'd increase it to 4 seconds, just to be safe. If you have something else running, it could slow down the process and trigger the error again, so best add the second for insurance. :)
 
Ken,
Thanks again that was exactly what I was needing! One final question, do you have any code that will allow you the option to print from the userform to your default printer. Our guys in the field have to print a paper copy for the customer and they will do the PDF for our own storage. If there is a way to add an additional command button to the user form that they can click that will send the selected sheets to the default printer, but not close out the userform so they can send the same sheets to the pdf/email command. This would make it a one stop shop for what we need.

Randall
 
Hi Randall,

Try this. I did make a change to the userform to accomodate this, and moved/renamed the code for the buttons to make it clearer on the back end a bit. So if you're copying this to your own project, I'd recommend deleting your userform, and copying this one into your file.

Hope it helps,
 

Attachments

  • xlgf703-1.xlsm
    65.3 KB · Views: 36
Ken,
I'm needing to change the way this workbook emails out. Prior I was using Outlook, but I'm noticing that the email is sitting in the outbox for an extended period of time. I have it set to auto send/receive. I've thought about using smtp instead for instant sending. Our company runs on and exchange server. I've found some code that is supposed to send via smtp, but have some quesitons. Is it as simple as replacing the existing code in the userform or does further change need to take place, also since we have so many users of this workbook, is there a way to automatically have the code fill in the username and password for their exchange email account?

Here is the code I've found, does it look like it will work for what I need?
Code:
[LIST=1]
[*]Public Function Mail_SMTP(strNTUserName As String, strNTUserPwd As String, _ 
[*]          strFrom As String, strTo As String, Optional strSubject As String, _ 
[*]          Optional strBody As String, Optional strBCC As String, _ 
[*]          Optional strCC As String, Optional strAttachment As String, _ 
[*]          Optional strHTMLBody As String, Optional strMailServer As String = "10.2.0.32") 
[*] On Error GoTo ErrHandler 
[*] Dim email As New CDO.Message 
[*] With email 
[*]   .From = strFrom 
[*]   .To = strTo 
[*]     If (Len(strAttachment) > 0) Then .AddAttachment strAttachment 
[*]     If (Len(strHTMLBody) > 0) Then .HTMLBody = strHTMLBody                '"<H4>See attached file</H4>" 
[*]     If (Len(strBCC) > 0) Then .BCC = strBCC 
[*]     If (Len(strCC) > 0) Then .CC = strCC 
[*]     If (Len(strSubject) > 0) Then .Subject = strSubject 
[*]     If (Len(strBody) > 0) Then .TextBody = strBody 
[*]   .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 
[*]    'Name or IP of Remote SMTP Server 
[*]   .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strMailServer 
[*]    'Type of authentication, NONE, Basic (Base64 encoded), NTLM 
[*]   .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/authenticate") = 0 
[*]    'Your UserID on the SMTP server 
[*]   .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = strNTUserName 
[*]    'Your password on the SMTP server 
[*]   .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = strNTUserPwd 
[*]    'Server port (typically 25) 
[*]   .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 
[*]    'Use SSL for the connection (False or True) 
[*]   .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False 
[*]    'Connection Timeout in seconds (the maximum time CDO will try to establish a connection to the SMTP server) 
[*]   .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60 
[*]     .Configuration.Fields.Update 
[*]       .Send 
[*] End With 
[*] ExitProcedure: 
[*]   Exit Function 
[*] ErrHandler: 
[*]   Err.Raise Err.Number, "Mail_SMTP", "An the following error occurred while attempting " & _ 
[*]                         "to send mail via Mail_SMTP." & vbCrLf & "Error Number: " & Err.Number & _ 
[*]                         vbCrLf & vbCrLf & "Error Description: " & vbCrLf & Err.Description 
[*]   Resume ExitProcedure 
[*] End Function 
[/LIST]

Thanks again!
 
Hey, sorry for the late reply. Things have been busy.

How about this... how about we modify the class module so that it triggers a SendAndReceive in Outlook?

To do this, go into the class module, and replace the Public Sub Send() routine with this one:

Code:
Public Sub Send()
'Method to preview the email
     Dim objOL As Object
    Dim objMail As Object
    Dim olNS As Object

    'Bind to Outlook
    Set objOL = CreateObject("Outlook.Application")
    Set olNS = objOL.GetNamespace("MAPI")
    olNS.logon

    'Create a new email
    Set objMail = objOL.CreateItem(0)
    CreateMessage objMail

    'Preview the message
    objMail.Send
    olNS.SendAndReceive (False)
    olNS.logoff
    
    'Release all objects
    Set objMail = Nothing
    Set objOL = Nothing
End Sub
 
Error showing -> Dim oEmail As New clsOutlookEmail -> Compile Error: User-defined type not defined
 
Back
Top