PDA

View Full Version : Solved: VBA and Email



Klartigue
03-30-2012, 09:10 AM
I have a spreadsheet that is automatically generated. This spreadsheet is named "Fidelity spreadsheet.xls." I would like to further automate a process to a point where after the spreadsheet is automatically generated by a macro, it automatically is sent in an email to the address FCMIntermediaryServices@fmr.com.

I am unfamiliar with how VBA works with email. IS there a code that will automatically send the "Fidelity spreadsheet.xls" to the above email?

Thanks for the help!!

Bob Phillips
03-30-2012, 09:23 AM
I have posted a couple of solutions in the past month along these lines, including a solution to the Outlooks security issue

http://www.vbaexpress.com/forum/showthread.php?t=41476&highlight=outlook

http://www.vbaexpress.com/forum/showthread.php?t=41029&highlight=outlook

Klartigue
03-30-2012, 09:35 AM
Great thanks!

This is what I have so far:

Sub SendMail()
Dim wbDept As Workbook
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim bodyText As String
Dim i As Long

Set wbDept = Workbooks("Fidelity spreadsheet.xls")

Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True


Set oMailItem = oOutlook.CreateItem(0)
With oMailItem

Set oRecipient = .Recipients.Add(wbDept.Cells(i, "C").Value)
oRecipient.Type = 1 '1 = To, use 2 for cc
Set oRecipient = .Recipients.Add(wsDept.Cells(i, "D").Value)
oRecipient.Type = 1
.Subject = "Avalon Trade Allocations Attached"
bodyText = "Please see attached trade alloctions. Let me know if you need anything else."


End With
End Sub


How to I send if the workbook is "Fidelity spreadsheet.xls" then send this to email address fcmintermediaryservices@fmr.com

I am not sure where to define the recipient as the above email address?

Klartigue
03-30-2012, 11:34 AM
I just dont know where to put the email address in the vba..any help?

Bob Phillips
03-30-2012, 12:26 PM
You already did in .Recipients.Add

Klartigue
03-30-2012, 12:30 PM
It isnt working.. i have changed things a little

[VBA]Sub SendMail()

Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim lastrow As Long
Dim bodyText As String
Dim i As Long


Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True


Set oMailItem = oOutlook.CreateItem(0)
With oMailItem

Set oRecipient = .Recipients.Add.fcmintermdiaryservices@fmr.com
oRecipient.Type = 1
.Subject = "Avalon Trade Allocations Attached"
bodyText = "Please see attached trade alloctions. Let me know if you need anything else."


End With

End Sub
[/VBA

i am just unsure where to put the email address .

Bob Phillips
03-30-2012, 01:08 PM
Set oRecipient = .Recipients.Add("fcmintermdiaryservices@fmr.com")
oRecipient.Type = 1

Klartigue
03-30-2012, 01:13 PM
Thanks, here is an updated version of what I have now.

Sub SendMail()

Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim lastrow As Long
Dim bodyText As String
Dim i As Long


Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True


Set oMailItem = oOutlook.CreateItem(0)

With oMailItem

Set oRecipient = .Recipients.Add("fcmintermdiaryservices@fmr.com")
.Subject = "Avalon Trade Allocations Attached"
bodyText = "Please see attached trade alloctions. Let me know if you need anything else."


End With

End Sub



Now, does the spreadsheet have to be open in excel when I run the macro? Right now I try to run the macro and nothing happens.

Do I need to incorporate some sort of:


Sub OpenPriceFile()
Workbooks.Open Filename:= _
"G:\Katherine Lartigue\Allocations\Fidelity spreasheet master.xls"
End Sub

Klartigue
03-30-2012, 01:31 PM
or maybe something like this in there?

Set wbDept = Worksheet("Fidelity spreasheet master.xls")

Klartigue
03-30-2012, 01:33 PM
hmm i tried both, neither are working. Any other ideas?

Klartigue
03-30-2012, 01:39 PM
Sub SendMail()
Dim wbDept As Workbook
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim lastrow As Long
Dim bodyText As String
Dim i As Long

Set wbDept = Workbooks("Fidelity spreasheet master.xls")

Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True


Set oMailItem = oOutlook.CreateItem(0)

With oMailItem

Set oRecipient = .Recipients.Add("fcmintermdiaryservices@fmr.com")
oRecipient.Type = 1

.Subject = "Avalon Trade Allocations Attached"
bodyText = "Please see attached trade alloctions. Let me know if you need anything else."


End With

End Sub


Updated version of what I have. When I run it, there are no errors but nothing happens...hmm.

Klartigue
03-30-2012, 01:52 PM
I basically need to send the fidelity master spreadsheet in an attachment to the fcmintermediaryservices@fmr.com email adress With the subject of the email Avalon Trade Allocations Attached and the body text of the email saying "Please see attached trade alloctions. Let me know if you need anything else."

Klartigue
03-30-2012, 02:14 PM
I got it to send an email!!!!!!

But it didnt send with the excel doc attached..still tryinig to figure it out. Document is Fidelity spreadsheet master.xls? Any help with sending an attachment?

Sub SendMail()
Dim wbDept As Workbook
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim lastrow As Long
Dim bodyText As String
Dim i As Long

Set wbDept = Workbooks("Fidelity spreasheet master.xls")

Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True


Set oMailItem = oOutlook.CreateItem(0)

With oMailItem

Set oRecipient = .Recipients.Add("klartigue@avalonadvisors.com")
oRecipient.Type = 1

.Subject = "Avalon Trade Allocations Attached"
bodyText = "Please see attached trade alloctions. Let me know if you need anything else."
.Send

End With

End Sub

Bob Phillips
03-30-2012, 02:20 PM
.Attachments.Add("full path and filename")

Klartigue
03-30-2012, 02:23 PM
Thanks for all your help!!! One last thing, the body text isnt working:

bodyText = "Please see attached trade alloctions. Let me know if you need anything else."

See anything wrong with it?

thanks again this will be so helpful to me!

Klartigue
03-30-2012, 02:36 PM
I looked at some other reference you sent me and figured out body text. Thanks for all the help!!!

Sub SendMail()
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim lastrow As Long
Dim bodyText As String
Dim i As Long


Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True


Set oMailItem = oOutlook.CreateItem(0)

With oMailItem

Set oRecipient = .Recipients.Add("klartigue@avalonadvisors.com")
oRecipient.Type = 1

.Subject = "Avalon Trade Allocations Attached"
bodyText = "Please see the attached trade allocations. Let me know if you need anything else.Thanks, Katherine Lartigue klartigue@avalonadvisors.com 713-238-2088"

.body = bodyText
.Attachments.Add ("G:\Katherine Lartigue\Allocations\Fidelity spreadsheet master.xls")
.Send

End With

End Sub

Bob Phillips
03-30-2012, 03:11 PM
Instead of


bodyText = "Please see the attached trade allocations. Let me know if you need anything else.Thanks, Katherine Lartigue klartigue@avalonadvisors.com 713-238-2088"

.body = bodyText


I would not bother with the intermediary variable. and stick some spacing in there


bodyText =

.body = "Please see the attached trade allocations." & vbNewline & vbNewline & _
"Let me know if you need anything else." & vbNewline & vbNewline & _
"Thanks, " & vbNewline & vbNewline & _
"Katherine Lartigue " & vbNewline & _
"klartigue@avalonadvisors.com 713-238-2088"

Klartigue
04-02-2012, 07:44 AM
Great thanks! That looks much better.