Send Email Depending On Cell Values
Jan 23, 2004
Does anyone know the code to send an email to a set list of six recipients when cells in a certain column in a worksheet reach a certain value greater than another. Let me explain a little - The action to be performed is date-based.
For Example:
When the date cell L2 is 30 days later than the date in K2, send an email with subject containing data from cells B2 and C3.
However, this has to work for columns K and L in their entirety, as both columns contain a list of dates...
View 9 Replies
ADVERTISEMENT
Nov 22, 2008
I have emails automatically being sent based on what is entered in the attendance columns, but now I need emails being sent on what is entered in the comments columns. Because the target range changes, I do not know how to combine them.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A11:A50, F11:F50, K11:K50, P11:P50, A55:A94, F55:F94")) Is Nothing Then Exit Sub
If LCase(Target) = "no" Then Call RouteActiveWorkbook(Target.Offset(, 1), Target.Offset(, 2), Target.Offset(, 3))
If LCase(Target) = "ex" Then Call RouteActiveWorkbook2(Target.Offset(, 1), Target.Offset(, 2), Target.Offset(, 3))
End Sub
This code results in two macros based on what it entered. Macro 1...........................
View 9 Replies
View Related
Sep 7, 2008
I used Scheduled Task to set up my spreadsheet to open daily. I have the code with assistance to pull out the due date items and place them into an email.
I have come across XLSTART/AUTOEXEC/ACTIVATE...ETC...
But cannot figure out the code that will automatically "enabling macro" once Scheduled Task opens the spreadsheet?
Then once the macro runs, the email with the due dates, how can this auto send without user interaction?
(currently I would have to hit send)
I am trying to make the process totally automated to open the spreadsheet at a certain time, send the email with due dates and close the spreadsheet.
Following code in ThisWorkbook--
Private Sub Workbook_Open()
Check_Date_Send_Mail
End Sub
Code in Module1--
Option Explicit
Sub Check_Date_Send_Mail()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnDate As Range, rnValue As Range
Dim stAddress As String, stMsg As String
Dim stRecipient As String, stSubject As String
Dim stPost As String
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Sheet1")
With wsSheet
Set rnDate = .Range("d2:t23")
End With
View 9 Replies
View Related
Jul 16, 2014
I have an excel sheet where automatically live data comes in.
When the cells reach a certain value the value in cell R1 becomes 1.
I want to make a macro in vba that sends me ONE email if the value becomes 1.
I tried :
Public Function MailS(mailtrigger As Double) As Double
If mailtrigger = 1 Then
Call makeandsendemail
Application.Run "makeandsendemail"
MailS = 1
Else
MailS = 1
End If
End Function
But this code send millions of emails.
View 9 Replies
View Related
Nov 29, 2009
I need an email to be sent to the last period teacher when a student earns less than 265 points. Some students go to a different class at the end of the day, so the teacher needs to be notified.
I have an if formula set up to create a yes statement: If(T26<265, "yes", ""). I need for an email to send when U26 is set to yes.
Actually I need an email to be sent anytime any cell in range (U5:U40) is yes, and I need it to be sent to the email address listed in the corresponding v column.
View 10 Replies
View Related
Oct 17, 2013
I have the following vba codes :-
Sub Mail_Cascavelle()
Range("b2") = Format(Date, "dd/mmmm/yyyy")
Const mydrive = "R:"
Const mydir = "Credit AdminSeebaruth RakeshSEARCHES NO SIGNATURE"
Dim myname As String
Dim ss As String
myname = Sheets("sheet1").Range("e5").Text & Format(Date, "dd-mmm-yy") & ".xls"
ss = mydrive & "" & mydir & "" & myname & ".xls"
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs Filename:=ss
[code]....
View 2 Replies
View Related
Oct 31, 2011
I am currently sending email to a few people from excel using the outlook and excel connectivity.
The issue is I have to individually define a module for each person I send an email to and it is extremely inefficient.
My attachment makes it clear.
There is 3 tabs in the workbook.
Tab 1 (Method 1) is what I am currently using and I know it extremely inefficient
Tab 2 (Method 2) is what I would like to use
* When I select a cell and click send email, I would like for that person to be sent the email
* When I select a range of cells, I would like all of those individuals to be sent an email
Tab 3 (Email) is what I would like Method 2 to use - this tab includes a subject line and the email body. It sends the message in the email tab to the recipients I have selected in Tab 2.
View 7 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 10, 2009
I have tried a number of things, but have not been able to figure it out. It may not be available with this particular code, but I am not sure what limitations this code has. Does anyone know how to include the contents of a particular cell in the subject line? Currently the subject line says "Notification." What I am looking for is for the subject to say "Notification - John Doe" where John Doe would be the contents of cell D13.
View 2 Replies
View Related
Sep 12, 2012
Here is a code line that works
.To = "giordano.bruno@gmail.com"
The following is passed over by the code, but does not send an email
.To = Recip
Recip is a variable taken from a cell value and, when I hover the cursor over it, shows "giordano.bruno@gmail.com" complete with inverted commas.
View 2 Replies
View Related
Aug 7, 2009
I am sending an email when the value of a cell changes, [url]
I am using the following code in my worksheet....
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("AE7"), rng) Is Nothing Then
If Range("AE7").Value < 20 Then EmailOut 'MyMacroName
End If
End If
What I would like to do is increase the range that this applies to.
Range from AE7 to Range Y3:AE250.
I would like to Include the Range A3:A250 in the subject/body.
So If AE7 has changed, A7 (along with some text "This cell has changed, do X Y Z") would be somewhere within the email.
View 9 Replies
View Related
Nov 19, 2008
I have created a detention tracking sheet for my school. I need an email to be automatically sent to the teacher when a student does not show up for detention.
Here is what I have currently coded, but the macro is not running, and the email is not being sent. I do not understand why.
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes" ....
View 9 Replies
View Related
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
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
Jun 22, 2014
I have Excel WB / Sheet with filled columns A (serial No.), B (name), C (e-mail adress), D (problem), E (solved / unsolved) and now I want that Excel automaticly sends e-mail notification to specific e-mail adress (column C) when its / his problem is solved (column E).
View 2 Replies
View Related
Feb 21, 2012
send the sheet to an email address based on the email address in a cell.
The cell populates from a dropdown list a user selects.
The email will be sent to myself, and one authoriser. If a second authoriser is required then the first authoriser will send this on. I'm trying to make this simple, so when a user clicks a 'send' button on the form this happens automatically.
The code I'm using to send to myself is
Application.Dialogs(xlDialogSendMail).Show arg1:="bibbyd01@myemailaddress.com", _
arg2:="Auth Details"
Nice and easy. So how do I replace that email addres with the email address in a cell? I assume I have to create a DIM to hold the data, then use it to populate the address field?
I'm using excel 2007 if it makes any difference.
View 1 Replies
View Related
May 20, 2008
I am trying to send an automated email by use of a "email" button. What I want it to do is to pop up a input box that will ask me who I want to send the email to, and once I hit ok it will send open up outlook and send the email. I have the code to work if I want it to be sent to a specific email address, but I can't seem to get the email address entry part to work. I will attach my code as it lays right now.
Private Sub CommandButton2_Click()'Need to reference: Microsoft Forms 2.0 Object LibrarySet OutApp = CreateObject("Outlook.Application")OutApp.Session.LogonSet OutMail = OutApp.CreateItem(0)strbody = "This is the most up to date copy of EAS Tracking 2.0 as well as the Resource Planning Sheet."attachmnt2 = "C:My DocumentsResource Planning Sheet_External.xls"On Error Resume Next'?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|? BELOW IS WHERE I CAN'T GET TO WORK!!!With OutMailDim range As Longrange = Application.InputBox("How many copies do you want?", "Number of Copies").To = range.Subject = "This Weeks Reports".Body = strbody.Attachments.Add (attachmnt2).Display.SaveEnd With'__________________________________________________attachmnt3 = "C:My DocumentsReport DataWork Request Tracking Data FolderEAS Request 2.0.xls"On Error Resume NextWith OutMail.Subject = "This Weeks Reports".Body = strbody.Attachments.Add (attachmnt3).Display.SaveEnd .........
View 9 Replies
View Related
Nov 20, 2008
I need to send an email reminder to teachers on the day they have duty. I know how to set up an email that sends on a certain date, but I am unsure how to code it when the date changes. I have attached the spreadsheet.
In cell A 53, I have the date November 24th. In cell C53, I have the email address of the teacher doing duty that day. In cell F53, I have the date November 25th. In cell, I53 I have the email address of the teacher performing duty that day. On November 24th, the person in C53 should receive a reminder email. On November 25th, the person in cell I53 should receive a reminder email. I will have spreadsheet for every month of the year. So there will be LOTS of changing dates. How do I code this when the date keeps changing?
View 2 Replies
View Related
Mar 12, 2014
If a cell changes to true i want it to send an email to the address that is in another cell,
For example if F5 CHANGES to true then send email to address in G5,
From here i can add in the subject line, and body i just cant figure out how to get it to send to a specific email address based on the cell value, and also only do it once, when it changes to TRUE rather than everytime the sheet is active, so i would require a macro to constantly be running, or run of off the cell when it changes
View 1 Replies
View Related
Apr 15, 2014
Is it possible for a Macro to read a cell say D:11 and copy that email address and send the active sheet to that email?
View 9 Replies
View Related
Oct 13, 2009
I am using the following code and it works great the only problem is that when I have more then one email address in the same cell it will not send the email. Even if I seperate it with a semicolon. It work fine if I have just one email address in the email field. How can I get it to send the same info to different email addresses.
View 4 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
Jul 12, 2014
I have used a database template from this site and changed it to suit my needs but I have a bit of a problem with some of the code. I know how to update the worksheet with the relevant userform text fields and in another project I did I have successfully sent userform text fields in the body of an email.
For this project I want to update the worksheet AND send an email at the same time. However, using the two pieces of code together is causing an error that I can't seem to solve (using my very limited vba knowledge!). The code I am working on is below and I have highlighted the line that is getting the error message. C
VB:
Private Sub cmdSubmit_Click() 'Submit new record
Dim ws As Worksheet, lRow As Long, Str As String [code]....
View 1 Replies
View Related
Feb 23, 2010
I have search on this forum regarding sending email on excel using outlook email application. I would like to ask if is it possible to use other email applications like AOL when sending email thru excel? I have attached a sample workbook.
View 9 Replies
View Related
Aug 12, 2009
I have a problem sending email thru excel, if i use this =HYPERLINK(CONCATENATE("mailto:",B2,"?subject=",C3,"&body=",D4),"Send e-mail") <-- this is working, but i want my body to also include not just d4, i tried to put name d4:g7 as body so the code would be: =HYPERLINK(CONCATENATE("mailto:",B2,"?subject=",C3,"&body=",body),"Send e-mail") <<-- the problem is this is not working ,
View 4 Replies
View Related
Jul 30, 2014
I'm trying to program a send e-mail botton and everything is working well. However I would like to change the send to address from a specific cell to a if than statement.
I would like it to read along the lines of........ if "I5" = yes then copy cell from "M5"
[Code] .....
View 1 Replies
View Related
Mar 20, 2014
I'm trying to send a basic email using example [URL]..... but just cannot get it to work.
Here's my code and the error I get is "Run-time error '-2147220978 (8004020e)': The server rejected the sender address. The server response was: 530 5.7.1 Client was not authenticated."
I found my smtp server by looking in Outlook properties so I know that's right and for all instances of "person@email.com" I am putting my email address and the password is the normal password I use to log on.
View 1 Replies
View Related
Jan 5, 2010
I'm running a Ron DeBruin's mail routine to copy and send a spreadsheet. The code I'm using is below. The problem is the source spreadsheet contains cells with more than 255 characters, and so that information is being truncated when the sheet is copied. I'm sure there's any easy fix for this, but I haven't been able to find it. Here's my code:
View 3 Replies
View Related
May 4, 2009
I feel like I almost have the Excel button I need (thanks to an exhaustive forum search), but I have 2 problems that I need help to correct. First, the code works fine if I have one email address in my Email column, but If I add a second or third email in my column, the code sends the mail, but bounces back because of an Invalid Internet address specified error. The response looks to me like it is taking the first address and appending to the second email recipient. An example would be <example@example.com,example>@example.com. Here is the code I am using:
View 3 Replies
View Related
May 8, 2009
I have the code that saves it a a PDF, but a nicety would be to email it as well: -
inputfileSaveName = Application.GetSaveAsFilename(fileFilter:="PDF Files (*.pdf), *.pdf")
'export it - works just fine!
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=filesavename, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'OK up to here!
Application.Dialogs(xlDialogSendMail).Show arg1:="", _
arg2:=EmailTitle, arg3:=Receipt
It now sends the "workbook" as an attachment and not the PDF..
How do I remove the attachment and attach the PDF Ive just created?
View 3 Replies
View Related
