Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and email
Hi
This code copies sheet1 and emails it. How can I mail values only without links? strdate = Format(Now, "dd-mm-yy h-mm-ss") Application.ScreenUpdating = False ActiveSheet.Copy Set wb = ActiveWorkbook With wb .SaveAs "Myfile" & Range("B56") & ".xls" Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail For Each Cell In ThisWorkbook.Sheets("Distribution List") _ .Columns("B").Cells.SpecialCells(xlCellTypeConstan ts) If Cell.Value Like "*@*" Then strto = strto & Cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) .To = strto .CC = "" .BCC = "" .Subject = "Loadfcst" & Range("B56") & ".xls" .Body = "" .Attachments.Add wb.FullName .Send End With .ChangeFileAccess xlReadOnly Kill .FullName .Close False Application.ScreenUpdating = True End With Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and email
Hi Saeed
See http://www.rondebruin.nl/mail/tips2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Saeed" wrote in message ... Hi This code copies sheet1 and emails it. How can I mail values only without links? strdate = Format(Now, "dd-mm-yy h-mm-ss") Application.ScreenUpdating = False ActiveSheet.Copy Set wb = ActiveWorkbook With wb .SaveAs "Myfile" & Range("B56") & ".xls" Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail For Each Cell In ThisWorkbook.Sheets("Distribution List") _ .Columns("B").Cells.SpecialCells(xlCellTypeConstan ts) If Cell.Value Like "*@*" Then strto = strto & Cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) .To = strto .CC = "" .BCC = "" .Subject = "Loadfcst" & Range("B56") & ".xls" .Body = "" .Attachments.Add wb.FullName .Send End With .ChangeFileAccess xlReadOnly Kill .FullName .Close False Application.ScreenUpdating = True End With Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and email
Is there any reason you can't do a Copy-Paste Special-Values before setting
ScreenUpdating = False? "Saeed" wrote: Hi This code copies sheet1 and emails it. How can I mail values only without links? strdate = Format(Now, "dd-mm-yy h-mm-ss") Application.ScreenUpdating = False ActiveSheet.Copy Set wb = ActiveWorkbook With wb .SaveAs "Myfile" & Range("B56") & ".xls" Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail For Each Cell In ThisWorkbook.Sheets("Distribution List") _ .Columns("B").Cells.SpecialCells(xlCellTypeConstan ts) If Cell.Value Like "*@*" Then strto = strto & Cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) .To = strto .CC = "" .BCC = "" .Subject = "Loadfcst" & Range("B56") & ".xls" .Body = "" .Attachments.Add wb.FullName .Send End With .ChangeFileAccess xlReadOnly Kill .FullName .Close False Application.ScreenUpdating = True End With Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and email
Thank you!
"Ron de Bruin" wrote: Hi Saeed See http://www.rondebruin.nl/mail/tips2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Saeed" wrote in message ... Hi This code copies sheet1 and emails it. How can I mail values only without links? strdate = Format(Now, "dd-mm-yy h-mm-ss") Application.ScreenUpdating = False ActiveSheet.Copy Set wb = ActiveWorkbook With wb .SaveAs "Myfile" & Range("B56") & ".xls" Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail For Each Cell In ThisWorkbook.Sheets("Distribution List") _ .Columns("B").Cells.SpecialCells(xlCellTypeConstan ts) If Cell.Value Like "*@*" Then strto = strto & Cell.Value & ";" End If Next strto = Left(strto, Len(strto) - 1) .To = strto .CC = "" .BCC = "" .Subject = "Loadfcst" & Range("B56") & ".xls" .Body = "" .Attachments.Add wb.FullName .Send End With .ChangeFileAccess xlReadOnly Kill .FullName .Close False Application.ScreenUpdating = True End With Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I email an inactive copy of a worksheet | Excel Discussion (Misc queries) | |||
can I copy a column of email addresses, paste into email address? | New Users to Excel | |||
Need a macro to Copy a selection and paste into a new email. | Excel Discussion (Misc queries) | |||
Macro - Copy - Email | Excel Worksheet Functions | |||
Copy/Paste to email | Excel Programming |