Email VBA in Excel - Small Help
 

Just wondering if somone could help me with some VBA. I have written a
macro so when I press a button, it creates an email with the email
addresses, relevant attachment and text in the body of the email.

However, I also want to add data from one tab called 'stats' (A5:A8 to
be precise) below my first line of the body of the message. Can anybody
help. My exisiting code is below:


Sub Mail_workbook_Outlook()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
To = "
CC = ""
BCC = ""
Subject = "Hello"
Dim strbody As String
strbody = "Please find attached today's spreadsheet and
statistics below." & vbNewLine & vbNewLine & _
"Kind regards" & vbNewLine & _
"Me"
Attachments.Add ("C:\desktop\name.pdf")
Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub


--
twogoodtwo
------------------------------------------------------------------------
twogoodtwo's Profile: http://www.excelforum.com/member.php...o&userid=12738
View this thread: http://www.excelforum.com/showthread...hreadid=565129

Email VBA in Excel - Small Help
 

you can do this


Dim strbody As String
Dim cell As Range


strbody = "Please find attached today's spreadsheet and statistics below." & _
vbNewLine & vbNewLine & "Kind regards" & vbNewLine & "Me" & vbNewLine & vbNewLine

For Each cell In ThisWorkbook.Sheets("stats").Range("A5:A8")
strbody = strbody & cell.Value & vbNewLine
Next


--
Regards Ron de Bruin
http://www.rondebruin.nl



"twogoodtwo" wrote in message
...

Just wondering if somone could help me with some VBA. I have written a
macro so when I press a button, it creates an email with the email
addresses, relevant attachment and text in the body of the email.

However, I also want to add data from one tab called 'stats' (A5:A8 to
be precise) below my first line of the body of the message. Can anybody
help. My exisiting code is below:


Sub Mail_workbook_Outlook()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
To = "
CC = ""
BCC = ""
Subject = "Hello"
Dim strbody As String
strbody = "Please find attached today's spreadsheet and
statistics below." & vbNewLine & vbNewLine & _
"Kind regards" & vbNewLine & _
"Me"
Attachments.Add ("C:\desktop\name.pdf")
Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub


--
twogoodtwo
------------------------------------------------------------------------
twogoodtwo's Profile: http://www.excelforum.com/member.php...o&userid=12738
View this thread: http://www.excelforum.com/showthread...hreadid=565129

Email VBA in Excel - Small Help
 

Thanks Ron,

Therefore, in trying to piece these two bits together, how would th
whole macro look as when try to and put the two together, it does no
work... atttempt below:

Sub Mail_workbook_Outlook()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = ""
Dim strbody As String
Dim cell As Range
strbody = "Please find attached today's spreadsheet."
vbNewLine & vbNewLine & _
"Kind regards" & vbNewLine & _
"Robin"
ThisWorkbook.Sheets("stats").Range ("A5:A8")
strbody = strbody & cell.Value & vbNewLine
Next
.Attachments.Add ("C:\Documents and Settings\test.pdf")
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Su

--
twogoodtw
-----------------------------------------------------------------------
twogoodtwo's Profile: http://www.excelforum.com/member.php...fo&userid=1273
View this thread: http://www.excelforum.com/showthread.php?threadid=56512

Email VBA in Excel - Small Help
 

Use this

Sub Mail_workbook_Outlook()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim strbody As String
Dim cell As Range

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

strbody = "Please find attached today's spreadsheet." & vbNewLine & vbNewLine & _
"Kind regards" & vbNewLine & _
"Robin" & vbNewLine & vbNewLine
For Each cell In ThisWorkbook.Sheets("stats").Range("A5:A8")
strbody = strbody & cell.Value & vbNewLine
Next

With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = ""
.Attachments.Add ("C:\Documents and Settings\test.pdf")
.Body = strbody
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"twogoodtwo" wrote in message
...

Thanks Ron,

Therefore, in trying to piece these two bits together, how would the
whole macro look as when try to and put the two together, it does not
work... atttempt below:

Sub Mail_workbook_Outlook()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
To = "
CC = ""
BCC = ""
Subject = ""
Dim strbody As String
Dim cell As Range
strbody = "Please find attached today's spreadsheet." &
vbNewLine & vbNewLine & _
"Kind regards" & vbNewLine & _
"Robin"
ThisWorkbook.Sheets("stats").Range ("A5:A8")
strbody = strbody & cell.Value & vbNewLine
Next
Attachments.Add ("C:\Documents and Settings\test.pdf")
Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub


--
twogoodtwo
------------------------------------------------------------------------
twogoodtwo's Profile: http://www.excelforum.com/member.php...o&userid=12738
View this thread: http://www.excelforum.com/showthread...hreadid=565129

Email VBA in Excel - Small Help
 

Change .Display to .Send


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Use this

Sub Mail_workbook_Outlook()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim strbody As String
Dim cell As Range

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

strbody = "Please find attached today's spreadsheet." & vbNewLine & vbNewLine & _
"Kind regards" & vbNewLine & _
"Robin" & vbNewLine & vbNewLine
For Each cell In ThisWorkbook.Sheets("stats").Range("A5:A8")
strbody = strbody & cell.Value & vbNewLine
Next

With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = ""
.Attachments.Add ("C:\Documents and Settings\test.pdf")
.Body = strbody
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"twogoodtwo" wrote in message
...

Thanks Ron,

Therefore, in trying to piece these two bits together, how would the
whole macro look as when try to and put the two together, it does not
work... atttempt below:

Sub Mail_workbook_Outlook()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
To = "
CC = ""
BCC = ""
Subject = ""
Dim strbody As String
Dim cell As Range
strbody = "Please find attached today's spreadsheet." &
vbNewLine & vbNewLine & _
"Kind regards" & vbNewLine & _
"Robin"
ThisWorkbook.Sheets("stats").Range ("A5:A8")
strbody = strbody & cell.Value & vbNewLine
Next
Attachments.Add ("C:\Documents and Settings\test.pdf")
Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub


--
twogoodtwo
------------------------------------------------------------------------
twogoodtwo's Profile: http://www.excelforum.com/member.php...o&userid=12738
View this thread: http://www.excelforum.com/showthread...hreadid=565129

Email VBA in Excel - Small Help
 

Thanks Ron - much appreciated for all your help - it works a treat.

Now I am going to see how I can add another feature of PDF'ing the
excel spreadsheet to the same file name using Excel 2003 and Adobe
Professional 7 using a macro - that should definately be possible?


--
twogoodtwo
------------------------------------------------------------------------
twogoodtwo's Profile: http://www.excelforum.com/member.php...o&userid=12738
View this thread: http://www.excelforum.com/showthread...hreadid=565129