Sending email from Excel

[Denise Posey]

See post from Jan 29 2004 4:52PM

thank you Rob for the following link:
Look here Denise
http://www.rondebruin.nl/sendmail.htm

I'm sure I'm missing something but is there an example where the code stops in Outlook to the "body" and not automatically send? I need to stop in the body to physically attach digital photos & then click the "send". Once you click the "send", return to the the active worksheet.

Thanks,
Denise

[Tom Ogilvy]

Which part of that link are you using. In this part:

Sub Mail_ActiveSheet_Outlook()
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "r...@debruin.nl"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

See where the line says
.Send 'or use .Display

Change .Send to .Display

--
Regards,
Tom Ogilvy

Denise Posey <dmpo...@msn.com> wrote in message
news:851b01c3e92a$cd166100$a501...@phx.gbl...

[denise posey]

I was going to use this one but wasn't sure about the 'attachments.add code. Do I delete this since there won't be a file to reference. The attachments are digital photos that will be download from a card reader. I'm just learning VB so I wasn't sure exactly what this code was doing. I'm not familiar with all the codes yet.

Denise

>.
>

[Tom Ogilvy]

If you don't want an attachment, then you can remove that line - however,
you don't have to attach a workbook. You could use that code to attach you
digital photos or not - then add them by hand.

--
Regards,
Tom Ogilvy

denise posey <dmpo...@msn.com> wrote in message
news:85d501c3e940$c3172ac0$a501...@phx.gbl...

[Denise Posey]

Since I am still a beginner at Vb, can you tell me the modification to the code?

I appreciate you patience!
Denise

>.
>

[YongKang Chen[MSFT]]

Hi Denise,

Thanks for posting in the group.

I have reviewed this thread. Currently I am finding somebody who could help
you on it. We will post back in the newsgroup as soon as possible.

If there is anything unclear, please feel free to post in the group and we
will follow up there.

Thank you for using Microsoft Newsgroup!

Wei-Dong XU
Microsoft Developer Support

[Tom Ogilvy]

If you don't want to attach a file, remove or comment out the line that says

.Attachments.Add wb.FullName

--
Regards,
Tom Ogilvy

"Denise Posey" <dmpo...@msn.com> wrote in message

news:7e1d01c3e94e$59515850$a301...@phx.gbl...

[Peter Huang]

Hi Denise,

Thanks for posting in the community.

It will be appreciated you could tell me whether this issue has been
resolved.

Please feel free to let me know if you have any further question on this
issue. I am standing by to be of assistance.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

[Denise Posey]

I am still unclear about the code on Rob deBruin's site as far as including attachments when you don't have a specific location to attach a file from.

I am a beginner with VB so I have narrowed it down to the following code for my ease:

Sub Mail_workbook()
ActiveWorkbook.SendMail "", _

"This is the Subject line"

End Sub

The two problems I have are: 1) I know the address that I want to mail to but I want to stop in the remarks section to attach digital photos or make additional comments before sending the worksheet. 2) When I entered the email address within the "" and used the .Display method, the code did not work. So I decided to omit the email address so the form will stop in outlook. I am not experienced enough to understand all the other codes within the site. Any other suggestions you can offer would greatly be appreciated. My main goal is to have the email address populated with the worksheet in the body as an attachment, and add any additional comments.

Thank you,
Denise

>.
>

[Peter Huang]

Hi Denise,

Thanks for posting in the community.

>The two problems I have are:

1) I know the address that I want to mail to but I want to stop in the
remarks section to attach digital photos or make additional comments before
sending the worksheet.
2) When I entered the email address within the "" and used the .Display
method, the code did not work. So I decided to omit the email address so
the form will stop in outlook. I am not experienced enough to understand
all the other codes within the site. Any other suggestions you can offer
would greatly be appreciated. My main goal is to have the email address
populated with the worksheet in the body as an attachment, and add any
additional comments.

If you wants to customize procedure to send mail , you should not the use
the ActiveWorkbook.SendMail method, since the method will just sent a
workbook.
You need to automation the outlook application to achieve your aim as
Tom's suggestion. Why do you need to stop? You may add the file to the
attachments you want to send as attachment.

This code below works on my machine, it will display the outlook and stop
here.

Sub Mail_ActiveSheet_Outlook()
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = ""

.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add wb.FullName

.Display
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Best regards,