Solved: VBA and Email

[Klartigue]

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]

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/show...hlight=outlook

http://www.vbaexpress.com/forum/show...hlight=outlook

[Klartigue]

Great thanks!

This is what I have so far:

[VBA]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
[/VBA]

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]

I just dont know where to put the email address in the vba..any help?

[Bob Phillips]

You already did in .Recipients.Add

[Klartigue]

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]

[vba]
Set oRecipient = .Recipients.Add("fcmintermdiaryservices@fmr.com")
oRecipient.Type = 1[/vba]

[Klartigue]

Thanks, here is an updated version of what I have now.

[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")
.Subject = "Avalon Trade Allocations Attached"
bodyText = "Please see attached trade alloctions. Let me know if you need anything else."


End With

End Sub
[/VBA]


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:

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

[Klartigue]

or maybe something like this in there?

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

[Klartigue]

hmm i tried both, neither are working. Any other ideas?

[Klartigue]

[VBA]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
[/VBA]

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

[Klartigue]

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]

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?

[VBA]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[/VBA]

[Bob Phillips]

[vba]
.Attachments.Add("full path and filename")[/vba]

[Klartigue]

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]

I looked at some other reference you sent me and figured out body text. Thanks for all the help!!!

[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("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
[/VBA]

[Bob Phillips]

Instead of

[vba]
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
[/vba]

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

[vba]
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"[/vba]

[Klartigue]

Great thanks! That looks much better.