Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Outlook or Word or Access Macro (VBA) - Open a series of emails in the outbox one at a time, change some parameters and email each one

Posted on 2011-09-18
5
Medium Priority
?
334 Views
Last Modified: 2012-05-12
Outlook or Word or Access Macro (VBA) - Open a series of emails in the outbox one at a time, change some parameters and email each one

Currently I have a process to use word merge to create emails to respond to bid questions. When I have Outlook set to work off line, each of the emails is saved in the outbox.

I then open each email, add an attachment, change priority, request a read deceipt and received receipt and sme times change the sent from address to an alaternate address before sending.

For 2-5 emails this is easily done manually, but for 20-25 emails its not so simple. I'd like to write a macro or vba code to run from word or access to automate this process. Can anybody help?

Thyanks
0
Comment
Question by:rogerdjr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36558282
So for each of the mails in outbox at the time of running the macro you want to take this set of actions and if so how is the change in sender to be controlled

Chris
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 2000 total points
ID: 36558413
OK, I do not have 2007 or later in front of me .. and accounts is a proerty associated with these versions so if sendusing is always to be set the following with the commented lines in amendOutbox enabled ought to work ... haven't been able to validate the function but hopefully I recall all correctly.


Sub amendOutbox()
Dim mai As mailitem 'Object
Dim acct As Long

'    acct = getAccount
    'If acct = 0 Then acct = 1
    For Each mai In Application.Session.GetDefaultFolder(olFolderOutbox).items
        If mai.Class = olMail Then
            With mai
                .Importance = olImportanceHigh
                .ReadReceiptRequested = True
                .OriginatorDeliveryReportRequested = True
                .Attachments.Add "c:\deleteme\testname.doc", olEmbeddeditem
'                .sendusingaccount = Application.Session.Accounts.item(1)
' Need to establish the account required ... but once set can be hard coded.
                .Send
            End With
        End If
    Next

End Sub

Function getAccount() As Long
Dim i As Long
    
' Accounts only valid for 2007 and on!
'can embed it as Access call if required via:
' acct = getaccount()
    
    For i = 1 To Application.Session.Accounts.Count
        If (MsgBox("Use Account " & Application.Session.Accounts.item(i).smtpaddress, vbYesNo)) = vbYes Then 'application.Session.accounts.item(i)
            getAccount = i
            Exit For
        End If
    Next

'    MsgBox "account selected as :> " & getAccount

End Function

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36561615
I have just tested getaccounts and it works fine so should do as required ... in the case of 2007 and later

Chris
0
 

Author Closing Comment

by:rogerdjr
ID: 36562416
Works great - thanks

A quick question - if I wanted to run this from access or word, can you help me add the code to:

1) Set Outlook to work off line
2) search for the outbox (after the merge has run)
3) run this code
4) set outlook to work on-line and send all the messages (one at a time).

I know this is essentially a new question but it is directly related.

Thanks
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36562641
Switch everything to late binding i.e.:

Chris
Sub amendOutbox()
Dim olkApp As Object
Dim mai As Object
Dim acct As Long

'    acct = getAccount
    'If acct = 0 Then acct = 1
    Set olkApp = CreateObject("outlook.application")
    For Each mai In olkApp.Session.GetDefaultFolder(4).items
        If mai.Class = 43 Then
            With mai
                .Importance = 2
                .ReadReceiptRequested = True
                .OriginatorDeliveryReportRequested = True
                .Attachments.Add "c:\deleteme\testname.doc", 5
'                .sendusingaccount = olkapp.Session.Accounts.item(1)
' Need to establish the account required ... but once set can be hard coded.
                .Send
            End With
        End If
    Next

End Sub

Function getAccount() As Long
Dim olkApp As Object
Dim i As Long
    
' Accounts only valid for 2007 and on!
'can embed it as Access call if required via:
' acct = getaccount()
    
    Set olkApp = CreateObject("outlook.application")
    For i = 1 To olkApp.Session.Accounts.Count
        If (MsgBox("Use Account " & olkApp.Session.Accounts.Item(i).smtpaddress, vbYesNo)) = vbYes Then 'olkApp.Session.accounts.item(i)
            getAccount = i
            Exit For
        End If
    Next

'    MsgBox "account selected as :> " & getAccount

End Function

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question