Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 30th, 2005, 06:40 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default 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
__________________
Mike
EchoVue.com
Reply With Quote
  #2 (permalink)  
Old December 1st, 2005, 02:25 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old December 2nd, 2005, 09:34 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
Reply With Quote
  #4 (permalink)  
Old December 2nd, 2005, 09:36 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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

mmcdonal
Reply With Quote
  #5 (permalink)  
Old December 2nd, 2005, 09:55 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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.
Reply With Quote
  #6 (permalink)  
Old December 2nd, 2005, 10:34 AM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old December 2nd, 2005, 10:43 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #8 (permalink)  
Old December 2nd, 2005, 10:58 AM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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
Reply With Quote
  #9 (permalink)  
Old December 2nd, 2005, 11:04 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
Reply With Quote
  #10 (permalink)  
Old December 2nd, 2005, 11:08 AM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Email Attachment rat07 PHP How-To 1 June 19th, 2007 01:27 PM
Email Attachment Code kunal_shiradkar SQL Server 2000 1 March 27th, 2007 09:59 AM
Dynamic EMAil Attachment nowzarthbawa XML 0 March 29th, 2005 01:58 AM
email an attachment GeoMeskin Javascript 1 December 17th, 2003 04:04 PM



All times are GMT -4. The time now is 07:33 PM.


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