Page 4 of 4 FirstFirst ... 2 3 4
Results 31 to 33 of 33

Thread: Email completed PDF

  1. #31

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?
    1. Public Function Mail_SMTP(strNTUserName As String, strNTUserPwd As String, _
    2. strFrom As String, strTo As String, Optional strSubject As String, _
    3. Optional strBody As String, Optional strBCC As String, _
    4. Optional strCC As String, Optional strAttachment As String, _
    5. Optional strHTMLBody As String, Optional strMailServer As String = "")
    6. On Error GoTo ErrHandler
    7. Dim email As New CDO.Message
    8. With email
    9. .From = strFrom
    10. .To = strTo
    11. If (Len(strAttachment) > 0) Then .AddAttachment strAttachment
    12. If (Len(strHTMLBody) > 0) Then .HTMLBody = strHTMLBody '"<H4>See attached file</H4>"
    13. If (Len(strBCC) > 0) Then .BCC = strBCC
    14. If (Len(strCC) > 0) Then .CC = strCC
    15. If (Len(strSubject) > 0) Then .Subject = strSubject
    16. If (Len(strBody) > 0) Then .TextBody = strBody
    17. .Configuration.Fields.Item("") = 2
    18. 'Name or IP of Remote SMTP Server
    19. .Configuration.Fields.Item("") = strMailServer
    20. 'Type of authentication, NONE, Basic (Base64 encoded), NTLM
    21. .Configuration.Fields.Item("") = 0
    22. 'Your UserID on the SMTP server
    23. .Configuration.Fields.Item("") = strNTUserName
    24. 'Your password on the SMTP server
    25. .Configuration.Fields.Item("") = strNTUserPwd
    26. 'Server port (typically 25)
    27. .Configuration.Fields.Item("") = 25
    28. 'Use SSL for the connection (False or True)
    29. .Configuration.Fields.Item("") = False
    30. 'Connection Timeout in seconds (the maximum time CDO will try to establish a connection to the SMTP server)
    31. .Configuration.Fields.Item("") = 60
    32. .Configuration.Fields.Update
    33. .Send
    34. End With
    35. ExitProcedure:
    36. Exit Function
    37. ErrHandler:
    38. Err.Raise Err.Number, "Mail_SMTP", "An the following error occurred while attempting " & _
    39. "to send mail via Mail_SMTP." & vbCrLf & "Error Number: " & Err.Number & _
    40. vbCrLf & vbCrLf & "Error Description: " & vbCrLf & Err.Description
    41. Resume ExitProcedure
    42. End Function
    Thanks again!

  2. #32
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    Excel Office 365 Insider
    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:

    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")
        'Create a new email
        Set objMail = objOL.CreateItem(0)
        CreateMessage objMail
        'Preview the message
        olNS.SendAndReceive (False)
        'Release all objects
        Set objMail = Nothing
        Set objOL = Nothing
    End Sub
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: -||- Blog: -||- Forums:
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #33
    Error showing -> Dim oEmail As New clsOutlookEmail -> Compile Error: User-defined type not defined

Page 4 of 4 FirstFirst ... 2 3 4

Posting Permissions

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