p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access (http://p2p.wrox.com/forumdisplay.php?f=18)
-   -   Email Attachment from VBA (http://p2p.wrox.com/showthread.php?t=35650)

echovue November 30th, 2005 06:40 PM

Email Attachment from VBA
 
OK - I know the real question is 'Why does the company I work for still use GroupWise?' but I don't think anyone has a logical answer to that.

Here is what I would like to do. I have a list of email addresses, and I would like to send an email with a Word Document attached to each of them.

Does anyone have any experience, tips or clues in regards to send an email through GroupWise, preferable with an attachement?

Thanks

Mike

Mike
EchoVue.com

mmcdonal December 1st, 2005 02:25 PM

Here is code that I use when an employee leaves our organization. I email a list of user assets to the help desk to retrieve. We have GroupWise. I haven't been able to answer the question either, other than the "Security by Obscurity" mentality.

'========== Code Starts ==========
Private Sub btnEmail_Click()
    Dim stSubject As String '
    Dim stName As String '
    Dim stSender As String '
    Dim stMessage As String '
    Dim stHelpDesk As String '
    Dim stFinished As String '
    Dim rs As ADODB.Recordset '
    Dim stSQL As String '
    Dim stAsset As String '
    Dim stSN As String '
    Dim stList As String '
    Dim objMessage
    Dim stLeaveDate As String
    Dim stRequestor As String


    stLeaveDate = InputBox("Please enter the date this employee" & vbCrLf & "is scheduled to leave the agency")
    stRequestor = InputBox("Please enter the name of the person requesting clearance:")

    stSQL = "Select * from Query1 Where UserID = " & Me.UserID '
    stSubject = "Exiting Employee" '
    stSender = "account@YourDomain.Com" '
    stName = Me.FirstName & " " & Me.LastName & " (" & Me.LOGIN_NAME & ")" '
    stMessage = "Please retrieve the following items from " '
    stHelpDesk = "Helpdesk@YourDomain.com" '
    stFinished = "The HelpDesk has been notified." '

    Set rs = New ADODB.Recordset '
    rs.Open stSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic '

    Do Until rs.EOF
        stAsset = rs!AssetCategory '
        stSN = rs!SerialNumber '
        stList = stList & vbCrLf & stAsset & " (SN:" & stSN & ")" '
        rs.MoveNext
    Loop

    Const cdoSendUsingPickup = 1 'Send message using the local SMTP service pickup directory.
    Const cdoSendUsingPort = 2 'Send the message using the network (SMTP over the network).

    Const cdoAnonymous = 0 'Do not authenticate
    Const cdoBasic = 1 'basic (clear-text) authentication
    Const cdoNTLM = 2 'NTLM

    Set objMessage = CreateObject("CDO.Message")
    objMessage.Subject = stSubject & ": " & stName
    objMessage.Sender = stSender
    objMessage.To = stHelpDesk
    objMessage.TextBody = stMessage & stName & " leaving " & stLeaveDate & ":" & _
                            vbCrLf & "Requesting Clearance: " & stRequestor & vbCrLf & stList

    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

    'Name or IP of Remote SMTP Server
    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.yourdomain.com"

    'Type of authentication, NONE, Basic (Base64 encoded), NTLM
    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic

    'Your UserID on the SMTP server
    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "****"

    'Your password on the SMTP server
    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "****"

    'Server port (typically 25)
    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

    'Use SSL for the connection (False or True)
    objMessage.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)
    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60

    objMessage.Configuration.Fields.Update

    '==End remote SMTP server configuration section==

    objMessage.Send

    MsgBox stFinished

End Sub
'========== Code Ends ==========

I am sure there is an attachment line, probably just "objMessage.Attachment = " and then the path to the file and the filename.

HTH


mmcdonal

SerranoG December 2nd, 2005 09:34 AM

Our office still uses GroupWise. I send attachments via VBA all the time. I just use the DoCmd.SendObject method. If your computer's default e-mail client is set to GroupWise, it will automatically be invoked.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

mmcdonal December 2nd, 2005 09:36 AM

Can you send more code on this method, SerranoG? I am always interested in improving code.

mmcdonal

SerranoG December 2nd, 2005 09:55 AM

Sure thing, but oddly enough there is not much to it. First I declare variables and assign their values to populate the parts of the DoCmd.SendObject method. They are marked with asterisks (*) below. Then I issue this statment:

DoCmd.SendObject acSendReport, strRptName, acFormatRTF, strTo, strCC, , strSubject, , True

- acSendReport is a report I wish to attach (Access constant)
- strRptName is the name of the report I wish to send*
- acFormatRTF means send the attachment as Rich Text Format (Access constant). Microsoft Word can open this easily.
- strTo is the recipient*
- strCC is the carbon copy recipient*
- strSubject is the subject line*
- True means don't send it right away; I wish to view the e-mail before sending it.

The only drawback to this is that the first time this code is excecuted, a Windows-generated dialog box opens asking you to confirm your default mail client. You have to click OK and then GroupWise will fire up and populate the e-mail. Subsequent use of this code will not invoke the dialog box until after you reboot. Even when I click "Use as Default", the dialog box will STILL appear after a reboot. I have not been able to figure out how to suppress this dialog box; but like I said, it only happens once per logon session.

echovue December 2nd, 2005 10:34 AM

Thanks Guys!

It sounds like we aren't as far out of mainstream as I had thought...

With the attachments, I think I am just having a brain cramp - but what I have is a Word Document that I would like to attach. Would I be correct in assuming I could just replace the strReport with the path to a file, or do I need to create an object of sorts to hold the Word Doc - If so, what kind of Object would it be?

Thanks again,

Mike

Mike
EchoVue.com

mmcdonal December 2nd, 2005 10:43 AM

This is working on one machine, and not on another. I get the run-time error 2046 that sendobject is not available.

On the machine where it does work, Outlook is also installed, and it picked up the message and asked if it was oaky that an app was trying to send an email. So I haven't gotten this to work with Groupwise yet, although groupwise did receive the message, since you can't uninstall Outlook. Well, you can but it involves renaming folders and deleting registry keys etc.

Any hints on the error?



mmcdonal

echovue December 2nd, 2005 10:58 AM

I may be up in the night and am not really sure what the error message is, but I will sometimes get a similar error when I try to send an email from Excel or another application. All that it takes to fix it, is to ensure that GroupWise is open. And then just to make it interesting, sometimes it just goes ahead and send it without a problem.

I also tried out Outlook a few months ago, and it completely took over all those kind of functions, even after I uninstalled it and reinstalled GroupWise. It took a complete reimage of the machine to get it to work!

I don't know if this posting has any point or useful information to it - But it is Friday!!

Mike

Mike
EchoVue.com

SerranoG December 2nd, 2005 11:04 AM

The SendObject not available error can just be a case of the proper library not being referenced.

As for GroupWise to pop up instead of Outlook, that could be the case of your Outlook being set as the default e-mail client on your computer. You'd have to reset it to GroupWise.

The SendObject method will not send external Word Documents (or any other external file) as attachments. I was playing with the GroupWise User Client libary in references to see if there was some kind of .Attachemnt method, but no luck.



Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

echovue December 2nd, 2005 11:08 AM

Hi Greg,

Thanks anyway, I may just have to convince the user that I need to convert their document to a report.

Have a great weekend.

Mike



Mike
EchoVue.com


All times are GMT -4. The time now is 11:59 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.