Hi,
I have been browsing the Web for a solution to the dilemma to send a mail directly from Excel without having to click “Yes” on the security pop up window and avoid sending the content as an attachment. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
At the moment I have two working examples of VBA code which “together” will do the trick – but I wonder how I can merge the two examples in order to “Automatically send a mail with only text in the body – where the text is picked up from the Excel sheet (for example the cells A1:A50 in a specific Sheet called “Presentation” in the active Excel file named “CompanyNumbers.xls) <o></o>
<o></o>
The trick is to avoid sending information as an attachment – and avoiding pop up security warning in Excel<o></o>
<o></o>
At the moment I have two working solutions where the first example show how to avoid the security warning – and the second example below shows how to sent plain text in the body of the mail. (instead of using an attachment).<o></o>
<o></o>
BUT: How can these two requirements be used together? It is a mysterious for me!<o></o>
<o></o>
OK - I have seen different comments on different Excel forums like:<o></o>
<o></o>
a) “It is not possible to bypass the security message unless you use CDO or ask your Admin people to change the firewalls etc.<o></o>
However: The first VBA code is an example that this is not the case. Or?<o></o>
<o></o>
b) I have also seen comments that if you want to send plain text – there are limitations of 225 characters etc.<o></o>
However: The second VBA code below proves that this is not the case because the used function is a good work around. Or?<o></o>
<o></o>
=> My question is if there are any smart people out there in the VBA world who can guide me how to get a working VBA code which combine the two VBA codes above? (To Email only text + bypass the security message in Outlook)<o></o>
<o></o>
Thanks, <o></o>
ECKSELL
Below is a working VBA code to automatically Email an attached file without having to click "Yes" on the pop up window in Outlook:
Below is a working VBA code to automatically Email plain text from specific cells in a specific active .xls file - but where the problem is that the pop up window in Outlook must manually be clicked "Yes" before sending the Email:
<o>
<o></o>
</o>
I have been browsing the Web for a solution to the dilemma to send a mail directly from Excel without having to click “Yes” on the security pop up window and avoid sending the content as an attachment. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
At the moment I have two working examples of VBA code which “together” will do the trick – but I wonder how I can merge the two examples in order to “Automatically send a mail with only text in the body – where the text is picked up from the Excel sheet (for example the cells A1:A50 in a specific Sheet called “Presentation” in the active Excel file named “CompanyNumbers.xls) <o></o>
<o></o>
The trick is to avoid sending information as an attachment – and avoiding pop up security warning in Excel<o></o>
<o></o>
At the moment I have two working solutions where the first example show how to avoid the security warning – and the second example below shows how to sent plain text in the body of the mail. (instead of using an attachment).<o></o>
<o></o>
BUT: How can these two requirements be used together? It is a mysterious for me!<o></o>
<o></o>
- The first working VBA code actually send an automatic mail (but with an attacment) without having to click “Yes”. The solutions works if you make sure to toggle the “Microsoft Office 11.0 Object Library” in (Excel=>Tools=>Visual Basic Editor=>Tools=>References). The working VBA code is displayed below in the first example. The problem is that I don't want to send the attached file - I would like to send plain text in the mail body instead.
- The second working VBA code works perfectly to Email the text in the body where the sent text is for example the cells A1:A50 in the sheet named “Presentation” in the Excel file called “CompanyNumbers.xls . The solution is based on Mr Ron de Bruin excellent example reffered to many, many times on different forums. BUT the problem is that this VBA code requires to manually click “Yes” to the pop up window unless you use some “external” modifications like CDO etc.<o></o>
OK - I have seen different comments on different Excel forums like:<o></o>
<o></o>
a) “It is not possible to bypass the security message unless you use CDO or ask your Admin people to change the firewalls etc.<o></o>
However: The first VBA code is an example that this is not the case. Or?<o></o>
<o></o>
b) I have also seen comments that if you want to send plain text – there are limitations of 225 characters etc.<o></o>
However: The second VBA code below proves that this is not the case because the used function is a good work around. Or?<o></o>
<o></o>
=> My question is if there are any smart people out there in the VBA world who can guide me how to get a working VBA code which combine the two VBA codes above? (To Email only text + bypass the security message in Outlook)<o></o>
<o></o>
Thanks, <o></o>
ECKSELL
Below is a working VBA code to automatically Email an attached file without having to click "Yes" on the pop up window in Outlook:
Rich (BB code):
Sub Send_an_attachment()<o:p></o:p>
<o:p></o:p>
Dim objol As New Outlook.Application<o:p></o:p>
Dim objmail As MailItem<o:p></o:p>
Set objol = New Outlook.Application<o:p></o:p>
Set objmail = objol.CreateItem(olmailitem)<o:p></o:p>
With objmail<o:p></o:p>
.To = "firstname.lastname@company.com"<o:p></o:p>
.cc = "firstname.lastname@company.com "<o:p></o:p>
.Subject = "The Yearly Company Numbers"<o:p></o:p>
.Body = "Hi, In the Cells A1:A50 you see the Yearly Company Numbers"<o:p></o:p>
.NoAging = True<o:p></o:p>
.Attachments.Add ("C:\CompanyNumbers.xls")<o:p></o:p>
.Display<o:p></o:p>
End With<o:p></o:p>
Set objmail = Nothing<o:p></o:p>
Set objol = Nothing<o:p></o:p>
SendKeys "%{s}", True<o:p></o:p>
<o:p></o:p>
End Sub<o:p></o:p>
Below is a working VBA code to automatically Email plain text from specific cells in a specific active .xls file - but where the problem is that the pop up window in Outlook must manually be clicked "Yes" before sending the Email:
<o>
Rich (BB code):
</o:p>
<o:p>Sub Mail_text_in_body()<o:p></o:p>
' Don't forget to copy the function RangetoHTML in the module.<o:p></o:p>
' Working in Office 2000-2007<o:p></o:p>
Dim rng As Range<o:p></o:p>
Dim OutApp As Object<o:p></o:p>
Dim OutMail As Object<o:p></o:p>
<o:p></o:p>
Set rng = Nothing<o:p></o:p>
On Error Resume Next<o:p></o:p>
'Only the visible cells in the selection<o:p></o:p>
'Set rng = Selection.SpecialCells(xlCellTypeVisible)<o:p></o:p>
'You can also use a range if you want<o:p></o:p>
Set rng = Sheets("Presentation").Range("A1:A50").SpecialCells(xlCellTypeVisible)<o:p></o:p>
On Error GoTo 0<o:p></o:p>
<o:p></o:p>
If rng Is Nothing Then<o:p></o:p>
MsgBox "The selection is not a range or the sheet is protected" & _<o:p></o:p>
vbNewLine & "please correct and try again.", vbOKOnly<o:p></o:p>
Exit Sub<o:p></o:p>
End If<o:p></o:p>
<o:p></o:p>
With Application<o:p></o:p>
.EnableEvents = False<o:p></o:p>
.ScreenUpdating = False<o:p></o:p>
End With<o:p></o:p>
<o:p></o:p>
Set OutApp = CreateObject("Outlook.Application")<o:p></o:p>
OutApp.Session.Logon<o:p></o:p>
Set OutMail = OutApp.CreateItem(0)<o:p></o:p>
<o:p></o:p>
On Error Resume Next<o:p></o:p>
With OutMail<o:p></o:p>
.To = "firstname.lastname@company.com"<o:p></o:p>
.cc = "firstname.lastname@company.com "<o:p></o:p>
.BCC = ""<o:p></o:p>
.Subject = "The Yearly Company Numbers"<o:p></o:p>
.HTMLBody = RangetoHTML(rng)<o:p></o:p>
.Send 'or use .Display<o:p></o:p>
End With<o:p></o:p>
On Error GoTo 0<o:p></o:p>
<o:p></o:p>
With Application<o:p></o:p>
.EnableEvents = True<o:p></o:p>
.ScreenUpdating = True<o:p></o:p>
End With<o:p></o:p>
<o:p></o:p>
Set OutMail = Nothing<o:p></o:p>
Set OutApp = Nothing<o:p></o:p>
End Sub<o:p></o:p>
<o:p></o:p>
Function RangetoHTML(rng As Range)<o:p></o:p>
' Changed by Ron de Bruin 28-Oct-2006<o:p></o:p>
' Working in Office 2000-2007<o:p></o:p>
Dim fso As Object<o:p></o:p>
Dim ts As Object<o:p></o:p>
Dim TempFile As String<o:p></o:p>
Dim TempWB As Workbook<o:p></o:p>
<o:p></o:p>
TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"<o:p></o:p>
<o:p></o:p>
'Copy the range and create a new workbook to past the data in<o:p></o:p>
rng.Copy<o:p></o:p>
Set TempWB = Workbooks.Add(1)<o:p></o:p>
With TempWB.Sheets(1)<o:p></o:p>
.Cells(1).PasteSpecial Paste:=8<o:p></o:p>
.Cells(1).PasteSpecial xlPasteValues, , False, False<o:p></o:p>
.Cells(1).PasteSpecial xlPasteFormats, , False, False<o:p></o:p>
.Cells(1).Select<o:p></o:p>
Application.CutCopyMode = False<o:p></o:p>
On Error Resume Next<o:p></o:p>
.DrawingObjects.Visible = True<o:p></o:p>
.DrawingObjects.Delete<o:p></o:p>
On Error GoTo 0<o:p></o:p>
End With<o:p></o:p>
<o:p></o:p>
'Publish the sheet to a htm file<o:p></o:p>
With TempWB.PublishObjects.Add( _<o:p></o:p>
SourceType:=xlSourceRange, _<o:p></o:p>
Filename:=TempFile, _<o:p></o:p>
Sheet:=TempWB.Sheets(1).Name, _<o:p></o:p>
Source:=TempWB.Sheets(1).UsedRange.Address, _<o:p></o:p>
HtmlType:=xlHtmlStatic)<o:p></o:p>
.Publish (True)<o:p></o:p>
End With<o:p></o:p>
<o:p></o:p>
'Read all data from the htm file into RangetoHTML<o:p></o:p>
Set fso = CreateObject("Scripting.FileSystemObject")<o:p></o:p>
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)<o:p></o:p>
RangetoHTML = ts.ReadAll<o:p></o:p>
ts.Close<o:p></o:p>
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _<o:p></o:p>
"align=left x:publishsource=")<o:p></o:p>
<o:p></o:p>
'Close TempWB<o:p></o:p>
TempWB.Close savechanges:=False<o:p></o:p>
<o:p></o:p>
'Delete the htm file we used in this function<o:p></o:p>
Kill TempFile<o:p></o:p>
<o:p></o:p>
Set ts = Nothing<o:p></o:p>
Set fso = Nothing<o:p></o:p>
Set TempWB = Nothing<o:p></o:p>
End Function<o:p></o:p>
<o:p></o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p>
</o>