1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Word Macro to attach doc to email, then send address email

Discussion in 'Business Applications' started by adamlink, May 16, 2012.

Thread Status:
Not open for further replies.
  1. adamlink

    adamlink Thread Starter

    Joined:
    May 16, 2012
    Messages:
    3
    I have a word document that includes a form the user must fill in. I want to create a button that will do all of the following:
    1. Update all fields
    2. Attach the updated word document to an email
    3. Enter an address I have stored in the macro into the To field of the email.

    I have one module which does the first part:
    Sub CommandButton1_Click()
    Dim oStory As Range
    For Each oStory In ActiveDocument.StoryRanges
    oStory.Fields.Update
    If oStory.StoryType <> wdMainTextStory Then
    While Not (oStory.NextStoryRange Is Nothing)
    Set oStory = oStory.NextStoryRange
    oStory.Fields.Update
    Wend
    End If
    Next oStory
    Set oStory = Nothing
    End Sub

    I found that if I add this to the before End Sub it attaches the updated Word document to an email:
    Application.Options.SendMailAttach = True 'Or False
    ActiveDocument.SendMail

    However, I can't figure out how have it put a specific address into the To field of the document.

    So I tried another tact. I have the email address as a hyperlink in the document, and set a bookmark at the hyperlink. Then I created a module that will go to the bookmark and open the hyperlink. However, this will not add the document as an attachment:

    Sub Open_Hyperlink()
    Selection.GoTo What:=wdGoToBookmark, Name:="hyperlink"
    With ActiveDocument.Bookmarks
    .DefaultSorting = wdSortByName
    .ShowHidden = False
    End With

    Selection.Range.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    End Sub

    So then I found this bit of coding on the web, and tried implementing it (changing the email address and text from what's shown here) but got Run-time error '5892': That method is not available on that object

    Sub mailit()
    ActiveDocument.HasRoutingSlip = True
    ActiveDocument.RoutingSlip.AddRecipient "[email protected]"
    ActiveDocument.RoutingSlip.Subject = "Hey this got Routed"
    ActiveDocument.RoutingSlip.Message = "Here's the body text"
    ActiveDocument.Route
    End Sub

    Can someone tell me how set up my macro so it does all three of my requirements as listed at the top of my post?

    Zane
     
  2. adamlink

    adamlink Thread Starter

    Joined:
    May 16, 2012
    Messages:
    3
    I found a solution, but I think it is a bit bulky. It seems to work but I'd love to know if there is a simpler solution:
    Sub CommandButton1_Click()
    Dim oStory As Range
    For Each oStory In ActiveDocument.StoryRanges
    oStory.Fields.Update
    If oStory.StoryType <> wdMainTextStory Then
    While Not (oStory.NextStoryRange Is Nothing)
    Set oStory = oStory.NextStoryRange
    oStory.Fields.Update
    Wend
    End If
    Next oStory
    Set oStory = Nothing

    ActiveDocument.Save

    MsgBox "Thank you. Your order has been sent", vbOKOnly, "Thank you. Your order has been sent"


    Dim bStarted As Boolean
    Dim oOutlookApp As Outlook.Application
    Dim oItem As Outlook.MailItem
    On Error Resume Next
    If Len(ActiveDocument.Path) = 0 Then 'Document has not been saved
    ActiveDocument.Save 'so save it
    End If
    Set oOutlookApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then 'Outlook isn't running
    Set oOutlookApp = CreateObject("Outlook.Application")
    bStarted = True
    End If

    Set oItem = oOutlookApp.CreateItem(olMailItem)
    With oItem
    .To = "[email protected]"
    .Subject = "Promo Order" '
    .Body = "Thank you for your order"
    .Attachments.Add Source:=ActiveDocument.FullName, Type:=olByValue
    .Send
    '**********************************

    End With
    If bStarted Then 'If the macro started Outlook, stop it again.
    oOutlookApp.Quit
    End If
    Set oItem = Nothing
    Set oOutlookApp = Nothing
    End Sub
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1053415

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice