Range To Be Displayed In Email Sent

Feb 20, 2012

Using the below code, I want data in range with format (for ex: J2:K7) to be displayed in the email sent in the place of Email_Body1 place.

Private Sub CommandButton1_Click()
Range("H2").Value = Date
Dim myOutlook As Object
Dim myMailItem As Object
Dim FName As String
Dim i As Long

[Code]....

View 9 Replies


ADVERTISEMENT

Excel 2010 :: VBA Loop Code To Automatically Email Range Of Cells And Email Addresses

Sep 16, 2013

I am using Excel 2010. I have been given a task at work that can save my team a lot of time if I can solve the problem. Every month, we have a spreadsheet with about 5000 rows that we have to email. In each row, there is a range that we have to email to a specific email. For example, I would have to copy and paste Range A2-R2 in the body of the email, and then email it to whatever email is in cell S2. I would then continue this for the next 5000 lines, making it a possibility that i will be sending 5000 emails manually.

I have been trying to come up with a solution through VBA that would automatically send these emails. My goal is to automatically send the Range A2-R2 to outlook email, then cell S2 into the "To" email address box, and then automatically send it. So far, i have successfully been able to send one row, but cannot figure out how to loop it for the remainder of the rows.

A couple other key points are that I have column headings as well (Range A1-R1). If possible, I want to be able to include the column headings in the email body as well. Example - first email would be range A1-R2. second email would be range A1-R1 and A3-R3, and so forth. The body of the email would also contain a standard script, such as "Please review the information below."

The goal here is to save everyone from having to send 5000+ manual emails. This would be a big boost for my team.

Code:

Sub Email()
Dim rng As Range
Dim OutApp As Object

[Code].....

View 5 Replies View Related

IF Function: Range That Should Display 'Heavy', Medium Is Displayed Instead

Jan 26, 2009

=IF(('MAIN 3'!N54<=H52),"Light",IF(('MAIN 3'!N54>H52,N54<H54),"Medium",IF(('MAIN 3'!N54>=H54),"Heavy",""))). The first two parts work fine: =IF(('MAIN 3'!N54<=H52),"Light", IF(('MAIN 3'!N54>H52,N54<H54),"Medium", Light and Medium is displayed when I enter the appropriate range but when I enter a range that should display 'Heavy', Medium is displayed instead.

View 2 Replies View Related

Data Is Displayed As The Code Loops Thruogh A Range When Condition Met

Feb 15, 2007

I have a userform which displays data in a sequence for my workbook.

For now it is performing almost the way i wish, the data is displayed as the code loops thruogh a range (K6:N2000) when the condition is met.

Private Sub CommandButton1_Click()

For r = 6 To 2000
For c = 11 To 14
Application.ScreenUpdating = False

If Cells(r, c).Offset(1, 0) Cells(r, c) Then

Cells(r, c).Select
Dim sWhat As String
Dim wWhat As String
sWhat = ActiveCell.End(xlUp).Value

MsgBox "Generate " & sWhat & " " & ActiveCell.Value & "'s" & " requirement data.", _
vbMsgBoxSetForeground, ("Azone requirment")..................

View 9 Replies View Related

Email To Range Of Cells - VBA

Dec 7, 2013

I would like to send an email to people if there email address appears in a range. ie if a checkbox = true, then their email address will appear in B2, and the next person in B3 etc... with the code to get the '.To:' to send the email to the addresses that appear in that range.

[Code] ......

View 3 Replies View Related

Email Range Via Outlook

Feb 15, 2010

I am using a code copied from Ozgrid to email a worksheet but the code doesn't seem to work?

Sub Mail_Sheet_Outlook_Body()
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
With Application
.EnableEvents = False
. ScreenUpdating = False
End With

Set rng = Nothing
Set rng = ActiveWorkbook.ActiveSheet.PrintRange 'UsedRange
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon .......................

View 8 Replies View Related

Email A Range Via Outlook

Jun 13, 2007

I have a range in an Excel worksheet named 'Request'. I want to paste this range into the body of an email and send to sepcified recipient via outlook. I tried the following code, but this does not seem to work. Am I not referencing to Outlook correctly?

Private Sub cmd_Send_Request_Click()
Dim objol As New Outlook.Application
Dim objmail As MailItem
Dim varBody As String
Dim objdata As DataObject
Set objol = New Outlook.Application
Set objmail = objol.CreateItem(olMailItem)
Set objdata = New DataObject
Application.Goto Reference:="Request"
Selection.Copy
objdata.GetFromClipboard
varBody = objdata.GetText
With objmail
.To = "kully.rai@ca.com"
.Subject = "Benchmarking Request"
.body = varBody & vbCrLf & vbCrLf
.NoAging = True
.Display
End With
Set objmail = Nothing
Set objol = Nothing
End Sub

View 4 Replies View Related

Adding Range To Email Body?

Aug 13, 2014

I'm trying to add a range to this email code that will then paste the information into the body of this email however instead of getting the data I need i'm getting "-1" in the body instead.

I've tried defining the range below and adding that line to the main code but it doesn't work, the code will run but i get "-1" in the body.

View 13 Replies View Related

Automatic Email If Value In Range Of Cells

Jul 1, 2009

I've been to Ron de Bruin's site. Very helpful; I got the code below from him which sends starts another bit of successful code - email_via_Outlook. The automatic email part works fine. But it only checks the one cell (E3). I've tried getting it to look at E2:E10 but to no avail. Someone said on another forum that the line "If Target.Cells.Count > 1 Then Exit Sub" stops it from checking a range of cells. I want it to check E2 to E10. I also tried another bit of code which read E2:E10 but emailed every time the worksheet was opened; I only want one email sent for every time a line dips below 30.

View 10 Replies View Related

Data Format From Range To Email

Jan 24, 2010

I've got a range of data contained in 2 columns to be sent out by email. The problem is that the data in the email looks like;

Gas Meter Reading
123456

and I want it to look like;

Gas Meter Reading 123456

View 5 Replies View Related

Send Email Only One Sheet Range

Jan 7, 2012

Simple code for sending email from excel . i have data three worksheets and i want to send email only one sheet range a1 to f35?

View 4 Replies View Related

VBA Concatenate Email List Range

May 25, 2012

I get the correct results in K1 when I have a list of email addresses in column D, but when it tries to email it states, "Unknown recipient name found in the list of recipient list. Use a valid name and try again."

Code:
Dim x As String, rng As Range, cel As Range
Dim myString As String
With ActiveSheet
Set rng = .Range("D2:D" & Range("A65536").End(xlUp).Row)

[Code] .......

View 2 Replies View Related

Email Excel Range Using Lotus

May 27, 2009

I have yet to find ANY information online relating specifically to emailing any given range in Excel via Lotus Notes. The codes that do exist out there either fail to fully copy the range or relate to attaching an excel file into a Lotus Notes Memo.

Below I have copied the code the I am working with, but I keep getting the following error: Error # 438, Object doesn't support this property or method. After making some minor tweaks, it might then give me another error, Error 424 Object Required, but the range then doesn't copy into the Lotus Notes memo at that point.

I have highlighted the area that I think the code is failing at, that being the actual act of copying/pasting the range selected into the Lotus Notes Memo. With the current code, the selected range does get fully copied into a new memo in Lotus notes, but these errors populate in Excel.

Can anyone offer any suggestions to resolve this issue? I have spent hours on this and I'm sure there are many others out there that might be able to benefit from a solution as well.

Function LotusEmailQuote() ....

View 9 Replies View Related

Email Range To Suppliers Of Inventory

Apr 8, 2007

I need to send an email to each of the suppliers their present inventories using Outlook. If inventory is "0", set high priority flag. Example:

Supplier Item Qty Email address
A 123 10 Johndrew@link.net
A 456 2 Johndrew@link.net
B B10 20 Reliable.xom
B B11 0 Reliable.xom
C C22 9 Cold@Cola.com

View 4 Replies View Related

Sending Email Based On Cell Value And Range

Aug 3, 2012

I am looking to set up a vba code for sending an email to a recipient based on whether the row AI19:AI30 shows the value 3,6,9 and i want it to send an email showing all of the 3 data all of the 6 data and all of the 9 data.

I have attached my code so far.

VB:
Private Sub Workbook_Open()
Dim w As Worksheet, c As Comment
For Each w In ThisWorkbook.Worksheets
If UCase(w.Name) <> "All Trades" Then
Select Case w.Range("AI19:AI30").Value
Case Is = 3, 6, 9

[Code]....

View 1 Replies View Related

Copy Range To Email Body Using Clipboard?

Jan 30, 2014

I've wrote a function that allows me to insert a certain range to the body of an email. This email is composed in the code, too.So far I am able to build the emails body as plain text. how to retain the cell format also (colors, font size, border styles, ...)

View 14 Replies View Related

Copy Range Of Cells And Paste Into Email

Jan 14, 2009

I have a table in excel that I would like to copy into an email and send to my co-workers on a regular basis. Somewhere on the net I have find a piece of code (I've omitted a bunch of stuff here):

View 2 Replies View Related

Using Range To Send Email To Multiple Addresses?

Mar 7, 2013

I have a list of email addresses in the range K10:K33, (one address per row).

How can I use that range as the 'To' field of an email address, putting a ';' between each name?

This is what I am using at the moment but I want to amend it to do the above;

Code:
Sub Mail_Selection_Range_Outlook_Body1()
Dim rng As Range
Dim OutApp As Object

[Code]....

View 2 Replies View Related

Copy Range Of Cell From One Workbook To Another And Email

Nov 3, 2013

I need to copy a range of cells from one workbook paste into another and email that new work book.

View 3 Replies View Related

Email As Attachement One Spreadsheet From Range List

Feb 26, 2009

I am trying to send as an attachment "sheet1" to a email list in "sheet2" range "A1-A50"
I looked at Ron De Bruin examples but didn't find one to email a single sheet from a list.

I looked at examples at Mr. Excel but didn't find it. I apologize if I overlooked it. If I did just point me to it.

View 9 Replies View Related

Wrong Range Selected While Sending Email

Feb 25, 2010

I am wrote below code to send email using VBA:

Sub SendEmail()
'References needed :
'Microsoft Outlook Object Library
'Microsoft Scripting Runtime
Dim olApp As Outlook.Application, olMail As Outlook.MailItem
Dim FSObj As Scripting.FileSystemObject, TStream As Scripting.TextStream
Dim rngeSend As Range, strHTMLBody As String
Dim Range1 As Range, Range2 As Range
alertsSheet = ActiveWorkbook.Name
Workbooks(alertsSheet).Activate
Sheets("STATS").Select

Issue with this code is it is selecting wrong source range while generating email.

MsgBox Range1.Address is giving results as "$A$1:$B$1,$BX$1:$CA$1" and
MsgBox Range2.Address is giving results as "$A$32:$B$38,$BX$32:$CA$38" but
MsgBox rngeSend.Address is giving results as "$A$1:$B$38"

And the email generated as output is having only the data from range "$A$1:$B$38". Is there anything wrong with the way I am specifying the source range ??

I need that the code should replace strHTMLBody with the data in Range1 and Range2. I checked the tempsht.htm file and found that this file also have the same wrong data i.e. data from range "$A$1:$B$38"

Needless to specify that I am working with data having few hidden columns and need to consider only visible data.

View 9 Replies View Related

Email Workbook From Excel Using Range As Address

Mar 2, 2010

I want my macro to be able to mail the active workbok through outlook using the email addressses in Cell R1 and having the body of the email have the text in cell R2:R8?

Dim OutApp As Object
Dim OutMail As Object
Dim ws As Worksheet
Dim range as range

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

On Error Resume Next
With OutMail
.To = ws.Range("R1:R8").Value
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = ws.Range("R2:R8").Value
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

View 9 Replies View Related

How To Open New Outlook Email By Filling Some Range Of Worksheet Using VBA

Aug 14, 2014

I have the following:

[C1] My dynamic email subject
[C5:C10] My dynamic email body message
My recipient is always same "burak@burak.com"

Now, I need a VBA code, that will automatically open a blank new outlook email window fill the fixed recepient fill the dynamic subject and body of the message from a range in my workbook.

Then just stop there so that I can attach some different files each time and send myself.

View 5 Replies View Related

Email Multiple Recipients From Range Using Lotus Notes

May 3, 2009

I have searched high and low on the forum and can not seem to find a solution to my problem. Here is my problem (besides being new to Lotus Notes): I need a button on my excel worksheet that gets information from Column H in the attached spreadsheet and puts all the emails in the To: (could be >than 100 email addresses) Field in Lotus Notes. I would like to have the subject read "New Email from Your Basin Coordinator" and have the CC list come from a second worksheet (CC).

View 4 Replies View Related

Code To Check For Text In A Range Prior To Email Sheet

Jul 9, 2009

I am using the code below tied to a button on the worksheet to email a sheet and would like it to check that there has been text entered intothe cells below prior to emailing the sheet. If the cells have no text entered i would like a message box to pop up stating that all fields need to be completed.

Range of cells:

H6,A9,F9,A12,F12,A16,A23,A26,C28,D30,D32,D34,A37,D39,F36,F28

Email code I am using:

View 9 Replies View Related

Search Outlook Email Folder For Particular Subject Then Return Date Email Received

Nov 7, 2013

I would like to search an outlook folder for a variable in the subject line and return the date when that email was received. Here is the code i have so far...

Code:
Sub Get_pos()

Set olApp = CreateObject("Outlook.Application")

Dim olNs As Outlook.Namespace
Dim Fldr As Outlook.MAPIFolder
Dim olMail As Variant

Set olApp = New Outlook.Application

[Code] ........

View 2 Replies View Related

VBA Code To Send Email To People Whose Email Address Is In Access Table

Apr 29, 2014

I have specified the email addresses in the code to whom the email will be sent when the user presses "Send Email" button but now I want to add all the addresses in the Access table and write down the code that will send an email to those people whose addresses are in the Access table.

[Code] .....

View 5 Replies View Related

VBA Outlook Email Auto-send Routine - Check If There Any Attachments In Email To Be Sent

Jul 23, 2014

I have a macro to send emails to a group people that based on various criteria, it will attach a number of files to a recipient's particular email. This is a small portion of the code that does the attachment adding:

Code:
If Range("B" & a) = "Y" Then
If citChev "" Then .Attachments.Add citChev
End If
If Range("C" & a) = "Y" Then
If citMits "" Then .Attachments.Add citMits
End If
If Range("D" & a) = "Y" Then
If citToyo "" Then .Attachments.Add citToyo
End If
If Range("E" & a) = "Y" Then

[code].....

Most people get several of the files attached to their email and all works fine. But, there are certain conditions when all the IF() stmts fail where a recipient will not get any files attached. I do not want to send the email if this is the case, but it currently is sending it.

Is there a way after all the IF() stmts have processed to check to see if this current email has any attachments assigned to it? (IF .Attachments "" Then...) does not work.

View 2 Replies View Related

Automatically Unprotect Worksheet Before Email Macro Then Protect After Email Is Sent

Mar 2, 2009

I currently have a button then when pressed automatically sends a summary report taken from the first page of Sheet 1.

Worksheet needs to be protected all the time, but Macro only works on an unprotected worksheet.

I was wondering what additional code and where to put in so that when
protected back again after Macro has been executed?

Here’s the Macro taken from [url]

View 11 Replies View Related

Unable To Email Specific Sheet To Email Address?

Jul 3, 2013

I have the below code to email a specific sheet to an email address, however the email stays in the outbox and isn't sent. Is there something missing from the code or is it a setting issue with my email? I'm using Outlook 2010.

[Code]....

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved