Emailing Files

[LoriH]

I have 300 reports I create every month, from 3 seperate spreadsheets. I
have created macros with help from this discussion group to automate most of
it. The last step I have is emailing them to my contacts. Is there a way to
use a macro to do this? I can have the email address in the files if needed.

 

[Ron de Bruin]

There are a few ways Lori

You can use code like this
http://www.rondebruin.nl/mail/folder2/files.htm


Or if you filter records in a table you can use something like this
http://www.rondebruin.nl/mail/folder3/row2.htm

If you have a different situation paste back

 

[Don M.]

I used Ron's links to do this very same thing and it works like a charm! I'm
sure there is a way to use values in a cell to determine who the message gets
sent to.

Dim SendEmail As String
SendEmail = Worksheets("Run Report").Cells(2, 22)
If SendEmail = 1 Then GoTo Line2012:


Dim TradeShowCopies As Integer
TradeShowCopies = Worksheets("TradeShow Copies").Cells(10, 2)
If TradeShowCopies = 0 Then GoTo Line1012:

Dim rng As Range
Dim iMsg As Object
Dim iConf As Object
Dim Flds As Variant

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

iConf.Load -1 ' CDO Source Defaults
Set Flds = iConf.Fields
With Flds

..Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

..Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
"mail.mail.int"

..Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Update
End With


Set rng = Nothing
On Error Resume Next

Set rng = Sheets("TradeShow
Copies").Range("a1:j8").SpecialCells(xlCellTypeVisible)

On Error GoTo 0

If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

With iMsg
Set .Configuration = iConf
.To = "username"
.From = "username"
.Subject = "Subject here"
.HTMLBody = RangetoHTML(rng)
.Send
End With

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Line1012:

Don

 

[LoriH]

Hi Ron,

Thank you so much the first one is perfect for what I am doing.

Thanks again!!