Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-03-17
3
Medium Priority
?
590 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 2000 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

971 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