Solved

Edit VB script to send from different mailbox - mail merge with attachments

Posted on 2011-03-17
3
548 Views
Last Modified: 2012-05-11
Hi, I have a script (not mine) that will iterate through an open mail merge document and attach a file before then sending it as an email in outlook. I would like to edit it so that the emails are sent not from my default email, but from another mailbox I have access to. I don't have the facilty to login to outlook using just the mailbox I want to send from (as i know this would be an easier way).

Is there a simple ".from =" I can add that would do this instead?


Sub MergeWithAttachment()

Dim Source As Document, Maillist As Document, TempDoc As Document
Dim Datarange As Range
Dim i As Long, j As Long
Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem
Dim mysubject As String, message As String, title As String
Set Source = ActiveDocument
' Check if Outlook is running.  If it is not, start Outlook
On Error Resume Next
Set oOutlookApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
    Set oOutlookApp = CreateObject("Outlook.Application")
    bStarted = True
End If
' Open the catalog mailmerge document
With Dialogs(wdDialogFileOpen)
    .Show
End With
Set Maillist = ActiveDocument
' Show an input box asking the user for the subject to be inserted into the email messages
message = "Enter the subject to be used for each email message."    ' Set prompt.
title = " Email Subject Input"    ' Set title.
' Display message, title
mysubject = InputBox(message, title)
' Iterate through the Sections of the Source document and the rows of the catalog mailmerge document,
' extracting the information to be included in each email.
For j = 1 To Source.Sections.Count - 1
    Set oItem = oOutlookApp.CreateItem(olMailItem)
    With oItem
        .Subject = mysubject
        .Body = Source.Sections(j).Range.Text
        Set Datarange = Maillist.Tables(1).Cell(j, 1).Range
        Datarange.End = Datarange.End - 1
        .To = Datarange
        For i = 2 To Maillist.Tables(1).Columns.Count
            Set Datarange = Maillist.Tables(1).Cell(j, i).Range
            Datarange.End = Datarange.End - 1
            .Attachments.Add Trim(Datarange.Text), olByValue, 1
        Next i
        .Send
    End With
    Set oItem = Nothing
Next j
Maillist.Close wdDoNotSaveChanges
'  Close Outlook if it was started by this macro.
If bStarted Then
    oOutlookApp.Quit
End If
MsgBox Source.Sections.Count - 1 & " messages have been sent."
'Clean up
Set oOutlookApp = Nothing

End Sub

Open in new window

0
Comment
Question by:pingulingo
  • 2
3 Comments
 
LVL 4

Expert Comment

by:sgvill
ID: 35157045
Try adding a
.From = "myemail@domain.com"

in your With oItem loop
0
 

Author Comment

by:pingulingo
ID: 35157297
Hi, no sorry that doesnt work. The macro runs but no email is sent.
0
 
LVL 4

Accepted Solution

by:
sgvill earned 500 total points
ID: 35157629
ok, here is a list of properties you can set...

http://msdn.microsoft.com/en-us/library/aa210946(v=office.11).aspx

but it looks like it might give you an outlook prompt when you reset the senderemailaddress (and other) property.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Use email signature images to promote corporate certifications and industry awards.
Outlook Free & Paid Tools
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now