Consulting

Page 3 of 4 FirstFirst 1 2 3 4 LastLast
Results 41 to 60 of 61

Thread: Merge saved as seprate file?

  1. #41
    ub CreateDocAndEMail()
    '
    ' create Macro
    ' Macro created 12/4/2007 by Kathy Schreiber
    Dim myRange As Word.Range
    Dim DocName As String
    Dim MailTo As String

    Dim appOL 'As Outlook.Application
    Dim E_Mail 'As Outlook.MailItem
    Dim Needed 'As Outlook.Inspector

    Set appOL = CreateObject("Outlook.Application")

    Letters = ActiveDocument.Sections.Count
    For Counter = 1 To Letters
    Set myRange = ActiveDocument.Paragraphs(1).Range
    myRange.MoveEnd wdCharacter, -1
    DocName = "C:\Documents and Settings\Administrator\My Documents\Test\Merge " & myRange.Text & ".doc"
    myRange.Paragraphs(1).Range.Delete
    ActiveDocument.Sections.First.Range.Cut
    With Documents.Add
    .Range.Paste
    Set myRange = .Paragraphs(.Paragraphs.Count - 2).Range
    myRange.MoveEnd wdCharacter, -1
    If InStr(myRange.Text, "@") Then
    MailTo = myRange.Text
    Else
    MailTo = ""
    End If
    .SaveAs FileName:=DocName, FileFormat:=wdFormatDocument
    .Close
    End With

    If MailTo = "" Then
    MsgBox "Document " & DocName & " not e-mailed. No mail id found"
    Set E_Mail = appOL.CreateItem(olMailItem)
    Set Needed = E_Mail.GetInspector
    E_Mail.Recipients.Add "MailTo"
    E_Mail.Subject = "ILS Training Document"
    E_Mail.Attachments.Add DocName
    E_Mail.Send
    End If

    Next
    Set Needed = Nothing
    Set E_Mail = Nothing
    Set appOL = Nothing

    End Sub

  2. #42
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    Just glancing at this, you set DocName to:

    "C:\Documents and Settings\Administrator\My Documents\Test\Merge " & myRange.Text & ".doc"

    when myRange has a value of the first paragraph in the document. I'm afraid I'm losing track of what that might contain.

    To be sure what you are trying to do, can you add an extra line and run it again and post back with the result?

    Immediately before the .SaveAs line, add this line:

    [VBA]Debug.Print DocName[/VBA]

    This will output the value of DocName to the Immediate Window. Can you then cut and paste it and post it here?
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  3. #43

    try this one

    I redid the macro I noticed my changes to the code were not staying changed so I put the macro in the global templete

    the code myRange.Text is correct here but I still get the error message and the following is hightlighted

    .SaveAs FileName:=DocName, FileFormat:=wdFormatDocument

    ?docname
    C:\Documents and Settings\Administrator\My Documents\Contact\Merge Haskell David November 15, 2007 .doc


    Sub CreateDocAndEMail()
    Dim myRange As Word.Range
    Dim DocName As String
    Dim MailTo As String

    Dim appOL 'As Outlook.Application
    Dim E_Mail 'As Outlook.MailItem
    Dim Needed 'As Outlook.Inspector

    Set appOL = CreateObject("Outlook.Application")

    Letters = ActiveDocument.Sections.Count
    For Counter = 1 To Letters
    Set myRange = ActiveDocument.Paragraphs(1).Range
    myRange.MoveEnd wdCharacter, -1
    DocName = "C:\Documents and Settings\Administrator\My Documents\Contact\Merge " & myRange.Text & ".doc"
    myRange.Paragraphs(1).Range.Delete
    ActiveDocument.Sections.First.Range.Cut
    With Documents.Add
    .Range.Paste
    Set myRange = .Paragraphs(.Paragraphs.Count - 2).Range
    myRange.MoveEnd wdCharacter, -1
    If InStr(myRange.Text, "@") Then
    MailTo = myRange.Text
    Else
    MailTo = ""
    End If
    .SaveAs FileName:=DocName, FileFormat:=wdFormatDocument
    .Close
    End With

    If MailTo <> "" Then
    Set E_Mail = appOL.CreateItem(olMailItem)
    Set Needed = E_Mail.GetInspector
    E_Mail.Recipients.Add "MailTo"
    E_Mail.Subject = "ILS Training Document"
    E_Mail.Attachments.Add DocName
    E_Mail.Send
    End If

    Next
    Set Needed = Nothing
    Set E_Mail = Nothing
    Set appOL = Nothing
    End Sub

  4. #44
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    Well that should be OK assuming the folder exists.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  5. #45
    ?docname
    C:\Documents and Settings\Administrator\My Documents\Contact\Merge Haskell David November 15, 2007 .doc

  6. #46
    The path and file name come from the address line in the computer

  7. #47
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    On the face of it, it looks like it should be working.

    Just to be sure it's not a permissions or other system-related problem, after it fails can you manually try and save the document in the same place and with the same name.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  8. #48

    Attachment

    I can save it to that file manully. I have attached the file

  9. #49
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    It could be the tab.

    Try changing ...

    [VBA]
    DocName = "C:\Documents and Settings\Administrator\My Documents\Contact\Merge " & myRange.Text & ".doc"

    [/VBA]

    to

    [VBA]
    DocName = "C:\Documents and Settings\Administrator\My Documents\Contact\Merge " _
    & replace(myRange.Text,vbtab,"") & ".doc"

    [/VBA]
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  10. #50
    I never thought would be glad for a error message! at least it is a different one! now it says
    Run time error 5941
    the requested member of the collection does not exist

    debug Set myRange = .Paragraphs(.Paragraphs.Count - 2).Range

  11. #51
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    An empty page?

    You need to code for any situation you may encounter. From what little I know, this surprises me but, if it can happen, then change ..

    [VBA]Set myRange = .Paragraphs(.Paragraphs.Count - 2).Range
    myRange.MoveEnd wdCharacter, -1
    If InStr(myRange.Text, "@") Then
    MailTo = myRange.Text
    Else
    MailTo = ""
    End If[/VBA]

    to

    [VBA]
    if .Paragraphs.Count > 2 then
    Set myRange = .Paragraphs(.Paragraphs.Count - 2).Range
    myRange.MoveEnd wdCharacter, -1
    If InStr(myRange.Text, "@") Then
    MailTo = myRange.Text
    Else
    MailTo = ""
    End If

    Else
    MailTo = ""
    End If
    [/VBA]
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  12. #52

    Error message Compile error

    End with without with

    Here is what I have

    Sub CreateDocAndEMail()
    '
    ' CreateDocAndEMail()Macro
    ' Macro created 12/4/2007 by Kathy Schreiber
    '
    Dim myRange As Word.Range
    Dim DocName As String
    Dim MailTo As String

    Dim appOL 'As Outlook.Application
    Dim E_Mail 'As Outlook.MailItem
    Dim Needed 'As Outlook.Inspector

    Set appOL = CreateObject("Outlook.Application")

    Letters = ActiveDocument.Sections.Count
    For Counter = 1 To Letters
    Set myRange = ActiveDocument.Paragraphs(1).Range
    myRange.MoveEnd wdCharacter, -1
    DocName = "C:\Documents and Settings\Administrator\My Documents\Contact\Merge " _
    & Replace(myRange.Text, vbTab, "") & ".doc"
    myRange.Paragraphs(1).Range.Delete
    ActiveDocument.Sections.First.Range.Cut
    With Documents.Add
    .Range.Paste
    If .Paragraphs.Count > 2 Then
    Set myRange = .Paragraphs(.Paragraphs.Count - 2).Range
    myRange.MoveEnd wdCharacter, -1
    If InStr(myRange.Text, "@") Then
    MailTo = myRange.Text
    Else
    MailTo = ""
    End If
    .SaveAs FileName:=DocName, FileFormat:=wdFormatDocument
    .Close
    End With
    If MailTo = "" Then
    MsgBox "Document " & DocName & " not e-mailed. No mail id found"
    Else: Set E_Mail = appOL.CreateItem(olMailItem)
    Set Needed = E_Mail.GetInspector
    E_Mail.Recipients.Add "User@Example.com"
    E_Mail.Subject = "ILS Training Document"
    E_Mail.Attachments.Add DocName
    E_Mail.Send
    End If

    Next
    Set Needed = Nothing
    Set E_Mail = Nothing
    Set appOL = Nothing
    End Sub

  13. #53

    I should of added

    .SaveAs FileName:=DocName, FileFormat:=wdFormatDocument
    .Close
    End With...... is where the debug stops

  14. #54
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    You haven't included the last part of the code I posted. The Else .. End If.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  15. #55
    YIPHEE! It did it!!!

    It send emails to 'User@Example.com' how do I get it to email the address on the document? Does the email address need to be a hyperlink?

  16. #56
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    Change [VBA]E_Mail.Recipients.Add "User@Example.com"
    [/VBA]to [VBA]E_Mail.Recipients.Add MailTo[/VBA]
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  17. #57
    Yew Doggies!!! it works!! One last thing I promise! the email attachment has 2 pages the saved document has two also but the original has one ?? any ideas?

  18. #58
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    You need to remove the section break but you need to be careful that you don't disturb the code for picking up the e-mail address. This should be alright - immediately before the .SaveAs, add this line:
    [VBA]
    .Range.Characters.Last.Previous.Delete
    [/VBA]
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  19. #59
    Thank you! You're the greatest!!!!

  20. #60
    Bet you thought I was gone !! and now that I'm back you'd like to

    The macro works great!! Does just what I wanted it to!
    But I have another problem! The server doesn't allow me to access Outlook that way. Because I can from the merged new created docment copy the email address then go to file and send as an attachment then paste the email address in the send to address and send?? Well? What do you think??

Posting Permissions

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