macro to email

[Guest]

I'm fairly new to VBE. I want to create a macro that emails sheets in a
workbook.
I am using a macro from Ron de Bruin of this community (his website). Here
is the problems I running into.

1-the sheets are not automatically emailed, I have to go into Outlook and
send. However, nothing is happening. I don't received the emails (I have a
Yahoo account).

2-The emails are not deleted after being send (I assume the macro does that).

I just want to be able to email the sheets once I click on the forms button.
I have enclosed the macro below.

Sub EmailReports()
Dim sh As Worksheet
Dim wb As Workbook
Dim strdate As String
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("m1").Value Like "?*@?*.?*" Then
strdate = Format(Now, "mm--dd--yy h-mm-ss")
sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet" & sh.Name & "of" _
& ThisWorkbook.Name & "" & strdate & ".xls"
.SendMail ActiveSheet.Range("m1").Value, _
"This is the Subject Line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next sh
Application.ScreenUpdating = True
End Sub

 

[Bob Phillips]

1-the sheets are not automatically emailed, I have to go into Outlook and
send. However, nothing is happening. I don't received the emails (I have a
Yahoo account).

Sounds like you have a wrong email address.

2-The emails are not deleted after being send (I assume the macro does

that).

No it doesn't. It kills the file it creates, but it doesn't delete the
email. It doesn't know what mail client you are using.

 

[Ron de Bruin]

Hi Mo

1) this is a Outlook setting, see Tools>Options
Sending automatic in Outlook is not always working correct

See number 4
http://www.rondebruin.nl/mail/problems.htm

If the mails are in your Outbox and the address is OK it must work


2)It only delete the file with one sheet you send and not the mail