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
ADVERTISEMENT
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
Jan 2, 2013
I have created a user Form where a user can enter all the details like (First name, last name, Email ID etc...). the entered data is submitted on Sheet1 and it is working for me.
Now which I want is the is it possible to pick up the last email id (Column D) and send an automatic email where email will be sent the last recipient?
Actually I want is that when a user will fill up the form I want to send him/her an email.
View 8 Replies
View Related
Jan 13, 2012
I have an excel file wherein there are lots of line items in sheet1. I need to send an e-mail from Excel but before that it has to be autofilter. Below is how my data looks like.
BANBill Start DateOrder No.Business Organization NameProduct TypeCCM NameE-mail Address1-1C03GH1/1/2012XXXXXXXXXXXVPLSKarthikeyan DevanDevan.Karthikeyan@xxxxxx.com1-1C03GH1/1/2012SSSXXXXXXXVPLSVignesh DD.Vignesh@xxxxxx.com1-1C03GH1/1/2012YYYYXXXXXXXVPLSKarthikeyan DevanDevan.Karthikeyan@xxxxxx.com1-1C03GH1/1/2012ZZZZZZXXXXXXXVPLSYashwanth RajRaj.Yashwanth@xxxxxx.com
Here, first the excel sheet should autofilter in E-mail address. In above example, Karthikeyan Devan e-mail address are reflecting twice but the data is different. Now i need to copy both the line and paste it in the body of the e-mail inclusive of heading and send it to that e-mail address.
Once its completed, automatically it should active another autofilter and send it to another e-mail address.
Below is how the body of e-mail should look like. (Here i am taking Karthikeyan Devan lines as example)
We are from Billing Audit Team and are trying to determine if any of these orders were triggered to Billing Early.
If there are issues with the Bill Start Dates below, please respond back with the correct Bill Start Date and we can make the necessary Billing corrections.
BANBill Start DateOrder No.Business Organization NameProduct TypeCCM NameE-mail Address1-1C03GH1/1/2012YYYYYXXXXXXXVPLSKarthikeyan DevanDevan.Karthikeyan@xxxxxx.com1-1C03GH1/1/2012YYYYYXXXXXXXVPLSKarthikeyan DevanDevan.Karthikeyan@xxxxxx.com
ALso, subject line would be: "Action required: EB". To Address would be only one person but CC would be two peope who are always would be same (XXXX.XXX.com and YYY.YYY.com). If this could be automated trust me we are backed by under huge workload
View 5 Replies
View Related
Jan 3, 2009
way to have excel shoot out email automatically? I would not know the first thing about VBA, so if it's a VBA code, please talk to me as if I were a 5 year old. I need excel to track dates in a column, and if they are within a certain time frame, i.e 3 months out from today, then excel should automatically send out a notification to the names associated with those dates informing them that they are due to complete that task before it expires. It should also inform their boss that they are due as well as myself as the tracker. Please, if you know the answer to this, word it as simple as possible.
View 9 Replies
View Related
May 28, 2014
In a spreadsheet a macro calculates some percentages and populates a small table, copies the table and generates an email. The table includes conditional formatting to fill a cell in green if it is above 95%, or red if it is below 95%. The spreadsheet works fine, but with every possible solution I have tried including converting to HTML, the color cell is always red in the outlook email. I am guessing it is because in the transition to the email, the small table no longer is located in the cells that are used to do the color format.
Whats odd is when I copy and paste into outlook it works fine, but I have been unable to figure out how to paste into the body of an email using excel vba. I have read many posts from people with similar issues, most have no solution. I am currently attempting to convert the table into a gif and try inserting that into outlook. Due to the proprietary nature of the spreadsheet, I am unable by policy to publish any code.
View 4 Replies
View Related
Oct 30, 2008
I have a spreadsheet with a number of different actions with various different dates associated to them. Is it possible to create a macro that will automatically send an email to my outlook inbox (or somebody elses) when the date is due or upcoming?
Also is it possible to include different content in the email based on say the title of the action etc?
View 9 Replies
View Related
May 29, 2009
I have an excel sheet which contains user details and date of expiry of a particular service. I would like to send an email to user when the date of expiry is met.
View 14 Replies
View Related
Sep 26, 2013
I work for a company that place consignment stock in hospitals. We can generate a report with short to expired products on it. I need a way to automaticly identify and send and email to the appropriate sales person to notify them that they have a product in a hospital that will expire.
View 3 Replies
View Related
Apr 22, 2014
See attached example. all details there and noted clearly.
current code:
Sub emailinstant()
Dim OutApp As Object
Dim OutMail As Object
On Error Resume Next
[Code] .......
View 6 Replies
View Related
May 14, 2014
Basically I have a spreadsheet to monitor behaviour in a high school. There is a column in the sheet with the heading 'PT Action Required?' which has a drop down menu where users can either select "Yes" or "No".
I would like the spreadsheet to automatically send me an email to inform me if a user selects "Yes".
So far I have:
[Code] ....
However, nothing happens.
View 2 Replies
View Related
Mar 14, 2011
I am wanting to send an automatic email notification to several lotus notes accounts once a specific cell in my excel spreadsheet has been entered in (It does not matter what is entered into the cell).
View 9 Replies
View Related
Mar 22, 2012
I have a spreadsheet set up to track questions coming into a mailbox, some of which we have to refer on to other areas. What I am wanting is for an email to be sent back to the mailbox (same email address each time) when the query has been referred to another area for 14 days (chaser) and 28 days (urgent chaser).
Details
Column B = Name of person query belongs to
Column J = Query text
Column L = this should be checked to see if it = "Referred" to indicate that the query is referred to another area
Column N = Name of the area/person the query is referred to
Column AE = holds the number of days that the query has been referred for
What i would ideally like is that when AE reaches 14 an email is sent in the following format
Subject: Name of person: Query outstanding for 14 days Body: You have a query outstanding with "Column N" as follows "Column J in bold text".
And then once it reaches 28 days
Subject: Name of person: Query outstanding for 28 days Body: You have a query outstanding with "Column N" as follows "Column J in bold text".
View 1 Replies
View Related
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
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
Jun 30, 2014
I need to create 69 charts with 4 different series in every chart displaying information like average stock growth and so on. Is there any smarter way i can do that than manually when i know that the range of these charts will be consistent?
View 14 Replies
View Related
Oct 21, 2013
I am designing a car park rota on a spredsheet.Each person has a limited number of spaces. Is it possible to automatically stop people going in and deleting out entries from previous weeks (to stop any cheating!) or would I have to manually go in at the end of each week and protect.
View 7 Replies
View Related
Apr 13, 2006
I have a cell with two Options - Applicable and Not Applicable. with the help of experts here in this forum I was able to hide the subsequent cells if the user chooses "Not Applicable".
However Now what I looking to do is - If the user chooses "Not Applicable" I want my subsequent cells to automatically change to NO.
Example : My C56 will have two Options - Applicable and Not Applicable
My C57 to C 70 will have a bunch of questions with two options - YES and NO.
If the user chooses "Not Applicable" in C56, I want the cells C57 to C70 automatically change to NO.
View 9 Replies
View Related
Jun 12, 2014
I have a large document control register and it is updated daily and posted to a cloud for many people to access. It is very time consuming to constantly jump in and out and highlight and unhighlight as many things as there are and keeping track of which items that need to be unhighlighted. The thing I'm trying to figure out is if there is a formula for this...If i change any cell in a row I want the row to highlight itself entirely, but after 24 hours I want the highlight to expire. Is there a formula or function I can use to make this happen?
View 4 Replies
View Related
May 15, 2013
I would like to fill some cells with a customer id if this customer is the same as the cell bellow. As my example below.
CUSTOMER idCUSTOMER NAME TYPE
737346 Nikos 81
1154765 Kyriakos 81
1154780 Thanos 81
I would like to fill automatically the empty cell bellow Kyriakos the customer id 1154765 in new column. I want in column "new" to fill with customer id and when the customer id is empty to fill the same customer id as above customer id.
Like the example below
CUSTOMER idCUSTOMER NAME TYPEnew
737346 Nikos 81 737346
1154765 Kyriakos 81 1154765
1154765
1154780 Thanos 81 1154780
View 5 Replies
View Related
Nov 19, 2013
I have workbook 1 with information.
I have workbook 2 that contains cells that are linked to workbook1.
I have workbook 3 that contains cells that are linked to workbook2.
When I open workbook3 I would like it to be update without opening first workbooks 1 or 2.
Can it be done?
View 2 Replies
View Related
Jun 3, 2014
I am currently streaming financial data into excel which updates on a minute by minute basis. Every minute a new row of data is automatically inputted based on market activity. Is there any formula/function which would expand to cover the new data as the amount of cells increases? Is there any way that I can have it so that the function/formula only looks at the last 20 or so cells, so its like a moving/rolling formula/function?
View 3 Replies
View Related
Dec 12, 2011
I have several data validation style drop down menus, and I basically don't want excel to recognize changes in these cells and execute a calculation of the entire workbook. Since there is a large number of calculations being performed in the workbook the sheet gets really slow.
Overall the way excel "automatically calculates" really screws me up a lot. This built in functionality could be a lot smarter in my opinion.
View 3 Replies
View Related
Jul 10, 2013
I have an excel file with many worksheets. I want to get a pop up message providing definitions of different subjects when they are entered from a drop down list in a specific range within a column to improve the users understanding of the subjects.
Worksheet 1
Range (where I want the pop up message to be valid): J85:J385
Subjects from drop down list in specified range: "x,y,z"
Pop up message: "Definitions of x,y,z"
Do I have to make a new module, or write the macro in the selected worksheet? What should be the settings of the macro (general, worksheet, declarations etc.)
View 7 Replies
View Related
Jul 22, 2014
I have a long set of data that I extract a "table" out of based on index matching controlled by 4 different criteria. This part is working as intended. However I need to format the cells with the corresponding "fetched data" based on another criteria. Makes sense? Have a look at a spreadsheet i made representing my problem. It has comments for the problems.
excel tableformatting problem.xlsx
View 1 Replies
View Related
Nov 29, 2012
I, daily get a list of individuals with some data against each one of them. E.g Amit would appear 7 times in the list, John would appear 10 times in the list and so on and so forth. I am required to sort the data as per names and then fill one background color. One color for one person so that it becomes easy to read data specific to an individual.Ia there a way that the color fills up automatically. note the names and number of entries are not same everyday.
View 8 Replies
View Related
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
View Related
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
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
Dec 5, 2012
I have 5 columns of data where each column of data has two number in it separate by a space where the headers for each column is c1, c2, c3, c4 and c5. for example
c1 c2 c3 c4 c5 c6 c7 etc
1 1 1 2 2 2 2 1 1 1
3 3 3 4 4 4 4 3 3 3
etc
where each of these number pairs is under a separate column. The preview option for this forum editor is showing quite a difference between intended presentation and actual..
What I am looking to do is for each line item is to put the content of each row into a single cell with vertical alignment of the pairs of numbers. for example
c6
1 1
1 2
2 2
2 1
1 1
3 3
3 4
4 4
4 3
3 3
where each group of five pairs is in a single cell.
I am looking to do this in as automated an approach as possible. I dont want to have to ctrl-enter for example 4 times for each cell in c6 for 1000 different line items..
View 5 Replies
View Related