Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 17th, 2011, 03:58 AM
Authorized User
 
Join Date: May 2011
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
Default To send email from excel sheet .

HI ,

I want to send a email of the selected data from my excel sheet . Like some selected rows in one of the sheet . How is that possible . Thanks in advance .
 
Old November 17th, 2011, 08:23 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

You can use Workbook.Sendmail if you want to send the entire workbook by mail. If you try to do with Outlook you can have an elegant solution

I have tried using Outlook (http://vbadud.blogspot.com/2007/04/vba-email-automation-vba-mail.html)

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old November 18th, 2011, 04:26 AM
Authorized User
 
Join Date: May 2011
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
Default

Hi i am using the below code for sending mail -

Sub Mail_Selection_Range_Outlook_Body()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2010
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object

Set rng = Nothing
On Error Resume Next
'Only the visible cells in the selection
Set rng = Selection.SpecialCells(xlCellTypeVisible)
'You can also use a range if you want
'Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(x lCellTypeVisible)
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

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "[email protected]"
.CC = ""
.BCC = ""
.Subject = "Due"
.HTMLBody = RangetoHTML(rng)
.Send 'or use .Display
End With
On Error GoTo 0

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

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2010
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook

TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
"align=left x:publishsource=")

'Close TempWB
TempWB.Close savechanges:=False

'Delete the htm file we used in this function
Kill TempFile

Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function

Now problem is i need to give the adress of the recipient . I want to grep all the recipients from the sheet like if under column 8 column name reason = due it should grep emails from column 9 column name email , Is there any way to send to all the recepients present there . And mail should be sent to all those recepients
 
Old November 22nd, 2011, 04:18 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

You can try to get that as semi-colon separated text

Code:
Dim arRange()
arRange = Range("E2:E13")
For i = 1 To UBound(arRange)
    sAddress = sAddress & ";" & arRange(i, 1)
Next i
sAddress = Right(sAddress, Len(sAddress) - 1)
and assign it
Code:
With OutMail
.To = sAddress
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
The Following User Says Thank You to Shasur For This Useful Post:
honey26 (November 22nd, 2011)
 
Old November 22nd, 2011, 12:25 PM
Authorized User
 
Join Date: May 2011
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
Smile

Thanks it worked ..........
 
Old December 16th, 2011, 05:09 AM
Authorized User
 
Join Date: May 2011
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
Default

Hi , If i want to send only to filtered values and not to whole column , Then what i need to do with above code ?
 
Old December 16th, 2011, 12:11 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

You need to use the specialcells(Xlcellvisibiletype) - http://vbadud.blogspot.com/2008/08/r...-of-excel.html

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old December 16th, 2011, 05:42 PM
Authorized User
 
Join Date: May 2011
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
Default

do i need to use arrange.SpecialCells(xlCellTypeVisible) but it doesnt work as arrange is a array ...
 
Old December 17th, 2011, 11:02 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

You need to assign the visible cells to the range

arRange = ActiveSheet.Cells.SpecialCells(xlCellTypeVisible). Range

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old December 20th, 2011, 12:24 PM
Authorized User
 
Join Date: May 2011
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
Default

Sub mail()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2010
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object

Set rng = Nothing
On Error Resume Next
'Only the visible cells in the selection
Set rng = Selection.SpecialCells(xlCellTypeVisible)
'You can also use a range if you want
'Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(x lCellTypeVisible)
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

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Dim arRange()

arRange = Range("I2:I100")
arRange = ActiveSheet.Cells.SpecialCells(xlCellTypeVisible). Range

For i = 1 To UBound(arRange)
sAddress = sAddress & ";" & arRange(i, 1)
Next i
sAddress = Right(sAddress, Len(sAddress) - 1)
On Error Resume Next
With OutMail
.To = sAddress
.CC = "[email protected]"
.BCC = ""
.Subject = "abc"
.HTMLBody = "hi"
'.HTMLBody = RangetoHTML(rng)
.Send 'or use .Display
End With
On Error GoTo 0

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

Set OutMail = Nothing
Set OutApp = Nothing
End Sub





Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Macro - Send Email and Attachments robmill101 Excel VBA 1 October 21st, 2008 08:10 AM
Send email from Excel Danny_n Excel VBA 18 May 16th, 2007 03:37 PM
send email via excel for action items with overdue miracles Excel VBA 0 May 16th, 2007 02:20 AM
VBA code to email excel status sheet daily basis ramavtar Excel VBA 0 December 9th, 2006 05:28 AM
Send Part of Excel Sheet with specified Subject?? JimCognac Excel VBA 1 January 28th, 2005 04:35 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.