Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Sending by email attachment (WinXP/Word2003)

    I need to set up a template to send a copy of the completed document by email, with specific text in the Subject line - and preferably specific text in the To: field. I've found this code:

    Options.SendMailAttach = True
    ActiveDocument.SendMail

    ... which looks hopeful, if I can set variables for the sendmail line (eg activedocument.sendmail subject:="blah blah", to:="more blah"), but the complete lack of anything resembling instructions on doing this in the help screen for sendmail leads me to suppose it's not going to be that simple! Sigh. Nothing ever is, with Micro$oft, is it?!

    Any help would be very much appreciated!!
    Beryl M


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sending by email attachment (WinXP/Word2003)

    The SendMail method displays a new e-mail with the document attached, it is not intended to send the mail automatically. I think you'll have to use Automation to control Outlook from Word if you want to create and send an e-mail message with an attachment in code.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Sending by email attachment (WinXP/Word2003)

    That would be fine (create but not send the email) - but I do need to have the subject filled in automatically!
    Beryl M


  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sending by email attachment (WinXP/Word2003)

    SendMail isn't intended for that. Outlook Automation is the way to go, I think.

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Sending by email attachment (WinXP/Word2003)

    <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15> I thought you'd say that. It seemed so logical, that you'd just have 'activedocument.sendmail subject:="blah", to:="blah"', but I suppose that would make life too easy ...

    Thanks, Hans!
    Beryl M


  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sending by email attachment (WinXP/Word2003)

    That's how it goes - the SendMail method in Excel does have Recipients and Subject arguments. SendForReview in PowerPoint and SendObject in Access have similar capabilities. But not SendMail in Word...

  7. #7
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sending by email attachment (WinXP/Word2003)

    It seems you can also use the Routing Slip method.

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Sending by email attachment (WinXP/Word2003)

    Hans: Arrrgh!! Why me?!

    Leif: That looks great, except that apparently the users insist it has to be created but not automatically sent - and I can't find a way to make this create the email without sending it immediately!

    <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> We don't seem to have a 'sulking' smilie ...! (only joking <img src=/S/smile.gif border=0 alt=smile width=15 height=15>)
    Beryl M


  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sending by email attachment (WinXP/Word2003)

    If you look at the Outlook Automation code further down the page in the link Leif provided, all you need to do (in addition to tuning the code to your own needs) to save the message but NOT send it is (surprise) change the .Send line to .Save. Voila!
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    New Lounger
    Join Date
    Aug 2006
    Location
    Southampton, Hampshire, United Kingdom
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sending by email attachment (WinXP/Word2003)

    I've spent a little while searching on the internet and not found a solution for it, so I've had to roll my own. This kind of uses part of what's been suggested before (the 'Send To...Mail Recipient (as attachment), but then uses Outlook to locate the created email and then modifies it through Outlook, not through Word.

    To do the Sleep function used below, you'll need the reference to the Sleep API:
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

    other than that, it will need to be running in a Word document (will probably work with a few modifications in other office apps), and have a reference to the Outlook object model - again, this could be refactored to use CreateObject calls if need be.

    Public Sub AttachAndSendMail(sSubject As String, sBody As String, colRecipients As Collection)

    'objects needed
    Dim objOutlook As Outlook.Application
    Dim objInspector As Outlook.Inspector
    Dim objEmail As Outlook.MailItem

    'variables
    Dim vRecipient As Variant
    Dim dtStart As Date

    'constants
    Const iMaxWaitTime = 10

    'set it so that it attaches the document, instead of putting it in the body
    Options.SendMailAttach = True

    'attach current document to mail, this should then pop the new mail window up
    ActiveDocument.SendMail

    'get an Outlook application
    Set objOutlook = CreateObject("Outlook.Application")

    'now this seems totally dodgy, but unfortunately the SendMail call above is asychronous
    'so we need to wait for Outlook to do the honours, which may be a few seconds
    'so we need to stick around and wait...!
    dtStart = Now()
    Do While (objEmail Is Nothing And Now() < DateAdd("s", iMaxWaitTime, dtStart))
    'try and get the email
    'the email's subject line will be the name of the current document

    'loop through the 'inspectors' (which are basically outlook windows)
    For Each objInspector In objOutlook.Inspectors
    'find the window caption that matches our name (one should do because it'll be something like '[Document Name] - Message (Rich Text)')
    If InStr(objInspector.Caption, ActiveDocument.Name) > 0 Then
    'set the object
    Set GetMailItem = objInspector.CurrentItem
    Exit For
    End If
    Next

    'rather than sitting in a while loop killing the CPU which will reduce our chances
    'of actually getting Outlook working, use the pause API to wait for half a second
    Sleep 500

    'pick up any waiting events before proceeding
    DoEvents
    Loop

    'throw an Error if we didn't find the e-mail after waiting for it
    If objEmail Is Nothing Then
    ERR.Raise vbObjectError + 107, "FindMail", "Could not find mailitem after waiting " & iMaxWaitTime & " seconds"
    End If

    'now setup the subject etc as the values passed in
    objEmail.Subject = sSubject

    'set a CrLf at the start of the body, because the attachment takes up a bit of space.
    objEmail.Body = vbCrLf & sBody

    'loop through the recipients collection adding them to the mail item
    'have to use a variant here as VBA won't let you use a string datatype in a for-each
    'this call causes one of those 'An application is trying to mess around with your email' boxes to appear
    'so if you want to avoid one, don't add any recipients!
    For Each vRecipient In colRecipients
    objEmail.Recipients.Add CStr(vRecipient)
    Next

    'tidy up / destroy objects
    Set objEmail = Nothing
    Set objOutlook = Nothing

    End Sub

    I hope this comes in useful...!

    cheers

    Matthew

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Sending by email attachment (WinXP/Word2003)

    Hi Matthew, thanks for that - it's been so long since I tried to get this working I'd almost forgotten about it, but I'll have a go again now.

    Just one question springs to mind - how does the naming convention work? if someone used this template a lot and had a tendency to give it the same name, would the macro find several emails that match the requirement? Or could we include the date in the name to ensure no clashes?

    Many thanks
    Beryl M


  12. #12
    New Lounger
    Join Date
    Aug 2006
    Location
    Southampton, Hampshire, United Kingdom
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sending by email attachment (WinXP/Word2003)

    Hi Beryl,

    A fair point, it would find the first e-mail with that subject line, however it would only be emails actually open in a separate window... You've gotta hope your user is doing one thing at a time ;-)

    Alternatively you can save the document as a unique filename and then search for that, however that takes you back to the initial problem which I was facing - not wanting to save the document!!

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sending by email attachment (WinXP/Word2003)

    >It seems you can also use the Routing Slip method.

    Excellent link.
    Thanks.
    "SendDocumentAsAttachment" worked first time on the (remote) client's machine, saving me a car-trip out of town to the 'burbs.
    (I couldn't test it here because I don't have Outlook installed/configured.)

Posting Permissions

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