Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Outlook,Word,VBA

Status
Not open for further replies.
1DMF

1DMF

Programmer
Jan 18, 2005
8,795
GB
I'm having a mare trying to find a way I can use VBA from out MS Access DB, to use a word document as an email body, and mail merge it to a datasource, but also allow attachments to be made to the email.

Now if I open a word doc, CTRL+A , CTRL+C

Open Outlook and in the email body CTRL+V

It works fine, I get the content of word, including images and text formating in the email, i can add attachments and then send the email, which is received fine.

So how can I automate this process using VBA in MS Access?


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!
 
Sort by date Sort by votes
This sounds like fun! Outlook (deliberately) tends to put obstacles in your way.

What sort of merge are you doing - is it a catalog merge, the whole of which is being sent to one recipient? Or a 'normal' merge with each merge result going to an individual recipient? Or are you doing the merge in Access? And what version of Office are you using

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website
 
Upvote 0 Downvote
Well i'm not sure that i'm having fun!!

I want to allow users to design a document in word, then merge it to an email and send to multiple recipients along with adding attachments.

I have office 2003.

I can create outlook objects ok and I can open word, I cant seem to get the two to marry!

i think I can open word, select the content but I cant get the selection and paste syntax right, here's hwat I have...

Code: 
'open word template
Set wordapp = CreateObject("Word.Application")

' set ebulletin file
sFile = cDrive & "Members Database\FSA-AccessTemplates\eBulletin\" & Me!sendEBS

'Open template document
wordapp.Documents.Open sFile

With wordapp
    .Selection.WholeStory
    .Selection.Copy
End With


    'open Outlook
    Set oApp = CreateObject("Outlook.Application")
        
    'Create mail message
    Set oMailItem = oApp.CreateItem(olMailItem)
    
    With oMailItem
        Set oReceipt = .Recipients.Add("Staff")
        oReceipt.Type = olTo
        .Subject = sSubject
        [b].Body = wordapp.Selection.Paste[/b]
        'Loop over providers
        Do While Not rs.EOF
            Set oReceipt = .Recipients.Add(rs.Fields("EmailName"))
            oReceipt.Type = olBCC
            rs.MoveNext
        Loop
  
        rs.Close
        Set rs = Nothing
  
        ' add any attachments
        If (Me.editEBS.ListCount > 0) Then
            i = 0

           Do While i < Me.attEBS.ListCount
                Set oAttach = .Attachments.Add(Me.attEBS.ItemData(i))
                i = i + 1
            Loop

        End If
        
        'display outlook msg
        .Display
    End With
   
    'quit
    wordapp.Quit False
    Set wordapp = Nothing
    Set oReceipt = Nothing
    Set oMailItem = Nothing
    Set oApp = Nothing
it errors on the line i highlighted with
expected function or variable
Click to expand...
and highlights the .paste part

What am I doing wrong?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!
 
Upvote 0 Downvote
I'm not sure if you can paste without using the menus (I'm not an Outlook person) but you certainly can't do it that way.

.Body is just a text property - which you can set to text. You may be able to do something like:
Code: 
[blue].Body = wrdapp.Documents(sFile).Content.Text[/blue]
(I don't have Outlook 2003 on any machine and don't have Outlook at all on this laptop so that may not be exactly right).

If you want formatted text you need to something different. It is possible, though slightly complex, to set tjhe .HTMLBody property. More than that will have to wait till I can look at Outlook.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website
 
Upvote 0 Downvote
I'm way out of my depth but I have an idea. I think wordapp.Selection.Paste is essentially an action taking place in word. Maybe what you want is:
oMailItem.Body = wordapp.WholeStory

_________________
Bob Rashkin
 
Upvote 0 Downvote
Tony -> But it isn't just text, it's images, hyperlinks, a whole formatted word doc.

Bob -> nope i get the same error!

What's really frustrating is with the following code..
Code: 
'Open template document
wordapp.Documents.Open sFile

wordapp.Selection.WholeStory
wordapp.Selection.Copy


    'open Outlook
    Set oApp = CreateObject("Outlook.Application")
        
    'Create mail message
    Set oMailItem = oApp.CreateItem(olMailItem)
    
    With oMailItem
        Set oReceipt = .Recipients.Add("Staff")
        oReceipt.Type = olTo
        .Subject = sSubject

        'Loop over providers
        Do While Not rs.EOF
            Set oReceipt = .Recipients.Add(rs.Fields("EmailName"))
            oReceipt.Type = olBCC
            rs.MoveNext
        Loop
        
        rs.Close
        Set rs = Nothing
  
        ' add any attachments
        If (Me.editEBS.ListCount > 0) Then
            i = 0

           Do While i < Me.attEBS.ListCount
                Set oAttach = .Attachments.Add(Me.attEBS.ItemData(i))
                i = i + 1
            Loop

        End If


oMailItem.Display

    'quit
    wordapp.Quit False
    Set wordapp = Nothing
    Set oReceipt = Nothing
    Set oMailItem = Nothing
    Set oApp = Nothing

I get the attachments, the BCC recipients, subject etc..etc.. the email opens...I right click in the email body , select paste and the word doc is pasted exactly as I want it in the email.

I just can't seem to do this with VBA Code, i've tried SendKeys "^V" , but that doesn't work!

Sooooo close, but still no cigar! :-(



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!
 
Upvote 0 Downvote
I think you may need to access the UI.

After doing [blue]oMailItem.Display[/blue] can you get a reference to the ActiveInspector? I think, if you have Word set as Outlook editor, it has a WordEditor property (2007 always has this) which gets you a (Word) Document object which you should be able to work with.

If you don't use Word, I think you need to drive the menu option, something like

Code: 
[blue][i]inspector_reference[/i].CommandBars("Edit").Controls("Paste").Execute[/blue]

(but this is all guesswork :))

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website
 
Upvote 0 Downvote
thanks for the reply, but I cracked it using the MailEnvelope method.
Code: 
' Display message, title

sSubject = InputBox(Msg, tit)
    
If Nz(sSubject, "") = "" Then
    MsgBox "You must supply an email subject"
    Set rs = Nothing
    Exit Sub
End If

Set wd = CreateObject("Word.Application")

      
sFile = cDrive & "Members Database\FSA-AccessTemplates\eBulletin\" & Me!sendEBS

Set doc = wd.Documents.Open _
      (FileName:=sFile, ReadOnly:=False)
    Set itm = doc.MailEnvelope.Item
    With itm
        .To = "Staff"
        .Subject = sSubject
        .Save
        ID = .EntryID
    End With
    
    doc.Close
    Set itm = Nothing
    Set wd = Nothing
    Set doc = Nothing
    
    Set objApp = CreateObject("Outlook.Application")

    Set l_Msg = objApp.GetNamespace("MAPI").GetItemFromID(ID)
    
    With l_Msg
    
        'Loop over recipients
        Do While Not rs.EOF
            Set oReceipt = .Recipients.Add(rs.Fields("EmailName"))
            oReceipt.Type = olBCC
            rs.MoveNext
        Loop
        
        rs.Close
        Set rs = Nothing
  
        ' add any attachments
        If (Me.editEBS.ListCount > 0) Then
            i = 0

           Do While i < Me.attEBS.ListCount
                .Attachments.Add (Me.attEBS.ItemData(i))
                i = i + 1
            Loop

        End If
        
        .Display
    End With


    'quit
    
    Set oReceipt = Nothing
    Set l_Msg = Nothing
    Set objApp = Nothing
Got there in the end :)

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!
 
Upvote 0 Downvote
ah missed some closing references in that code
Code: 
doc.Close wdDoNotSaveChanges
wd.Quit False
Set itm = Nothing
Set doc = Nothing
Set wd = Nothing

is how it should go after the mailenvelope (end with)

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!
 
Upvote 0 Downvote
I've made an FAQ for those interested :)

faq705-6950

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
Outlook VBA - Trust access
Replies
3
Views
823
hermanlaksko
hermanlaksko
Andrzejek
  • Question
Open Word as ReadOnly 1
Replies
6
Views
362
Chriss Miller
C
Andrzejek
  • Locked
  • Question
Hyperlinks in Word cut at #
Replies
2
Views
249
Andrzejek
Andrzejek
thanhthanh12
  • Locked
  • Question
Help vba
Replies
3
Views
526
combo
combo
Andrzejek
  • Locked
  • Question
Outlook Template replace text 2
Replies
13
Views
2K
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top