Over 1 million tech questions and answers.

Auto mail from Excel to Outlook based on date in cell

Q: Auto mail from Excel to Outlook based on date in cell

Dear All,

I am creating a tracker file for Purchase Orders in which end date in column 'J' is very important.
Column 'J' has following conditional formatting
1. If cell date is in between todays date + 14 days --- YELLOW
2. cell date <= today's date ------------------------- RED
3. cell valus > today + 14 days ---------------------- GREEN

So now when the cell turn Yellow I want the excel should send the mail to the concerned person whose mail id will be mentioned in same row of column 'G'

One more requirement there should be two mails. Reminder 1 & Reminder 2.

I use officer 2010 & mail is outlook 2010 & OS is windows 7.
This file will be on share point. This file may not be opened everyday.

Please reply with the procedure as I am not a programming/ IT person... I may not understand all terms.. please try to simplify the response.
Thanks in advance for all the help.

RELEVANCY SCORE 200
Preferred Solution: Auto mail from Excel to Outlook based on date in cell

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Auto mail from Excel to Outlook based on date in cell

Hi there, welcome to the board!

You'd want a location to mark when an email was sent. I'm assuming you want a worksheet change event for this, which will basically always run when a cell on this specific worksheet is changed. There are other events you could use to fire it off, like the calculate event. You could, if you wanted to, assign this functionality to a button, but then it wouldn't be automatic.

The below code does what you ask. It goes in the worksheet module of the worksheet your data is on. To get to it, right click the sheet tab name and select 'View Code', then paste this there.

EDIT: The locations to mark when an email was sent (btw) were columns K and L, as you'll see them set as constants at the top of the code. You can change those letters to any column you want to house it in. It just puts the current system date in those cells, and that will be checked when the values in column J are checked. If nothing is there it assumes an email hasn't been generated yet and will do so. But if it has a value - any value, it will ignore that row.

Also, I assumed a 'yellow' highlighted value was your first reminder, and a 'red' highlighted value was your second reminder. It uses this as text in the subject and body of the email.
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Const ReminderOne As String = "K" 'column letter
Const ReminderTwo As String = "L" 'column letter

Dim OL As Object
Dim MailItem As Object
Dim ArrEmail() As Variant
Dim DateCheck As Range
Dim CellCheck As Range
Dim OutlookOpen As Boolean
Dim ArrCount As Long
Dim EmailStep As Long
Dim ColCheck As String
Dim EmailBody As String

Application.EnableEvents = False
Application.ScreenUpdating = False

'Date range to check - column J
Set DateCheck = Me.Range("J2", Me.Cells(Me.Rows.Count, "J").End(xlUp))

'Check cells in range
For Each CellCheck In DateCheck.Cells

'Yellow
If CellCheck.Value <= VBA.Date() + 14 And CellCheck.Value >= VBA.Date() Then
ColCheck = ReminderOne
'Red
ElseIf CellCheck.Value < VBA.Date() Then
ColCheck = ReminderTwo
'Green
Else
GoTo SkipCell
End If

'Check if email was already sent
If Len(Me.Range(ColCheck & CellCheck.Row).Value) > 0 Then GoTo SkipCell

'Add to array
If IsEmpty(ArrEmail) Then
ReDim ArrEmail(1 To 3, 1 To 1)
Else
ArrCount = ArrCount + 1
ReDim Preserve ArrEmail(1 To 3, 1 To ArrCount)
End If

ArrEmail(1, ArrCount) = CellCheck.Offset(0, -3).Value
ArrEmail(2, ArrCount) = ColCheck & CellCheck.Row
ArrEmail(3, ArrCount) = IIf(ColCheck = ReminderOne, 1, 2)

SkipCell:
Next CellCheck

'Check if any reminders need to go out
If ArrCount = 0 Then GoTo NoReminders

'Get Outlook application
On Error Resume Next
Set OL = GetObject(, "Outlook.Application")
OutlookOpen = True
If OL Is Nothing Then
Set OL = CreateObject("Outlook.Application")
OutlookOpen = False
End If
On Error GoTo 0

'Create emails
For EmailStep = 1 To ArrCount

'Create email message
Set MailItem = OL.CreateItem(0)

'To
MailItem.To = ArrEmail(1, EmailStep)

'Subject
MailItem.Subject = VBA.Choose(ArrEmail(3, EmailStep), "First Reminder", "Second Reminder")

'Body
'----------
'Lower case
'EmailBody = "This is your " & VBA.LCase(MailItem.Subject) & "." & vbNewLine & vbNewLine & "Thanks"
'Upper case
EmailBody = "This is your " & VBA.UCase(MailItem.Subject) & "." & vbNewLine & vbNewLine & "Thanks"

MailItem.Body = EmailBody

'Display email
MailItem.Display

'Mark the date when email was generated (will be skipped in future passes)
Me.Range(ArrEmail(2, EmailStep)).Value = VBA.Date()

Next EmailStep

'Close Outlook
If OutlookOpen = False Then
OL.Quit
End If

NoReminders:
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
HTH

Read other 1 answers
RELEVANCY SCORE 118.4

Hello,

I have seen posts similar to my requests but have been unable to put together a code to do everything I need it to do as I am very new to VBA coding.

I am looking for a VBA code that will generate an email when the date in a cell of a column is 7 days or less from the current date. The email will be sent to a recipient listed in another cell column and cc my coworker. I also need a code to change the data in another cell to "Approve, Email Sent" when the same cell date is between 7 and 1 days from the current date and "Expired, Email Sent" when the date equals the current date so that an email will only send if this cell does not equal "Approved, Email Sent" or "Expired, Email Sent" so that emails are not repeatedly sent every time the spreadsheet is opened. I plan on running task scheduler to open the spreadsheet every Monday so the code will run weekly at minimum.

Any help is greatly appreciated, thank you!
 

Read other answers
RELEVANCY SCORE 115.6

Dear Friends,

I'm in a serious need of updating the respective teams with reminders on due dates of statutory compliance requirements. I've developed an excel workbook having three sheets namely Permissions, Returns and Records. All the three sheets are having due dates. (Workbook is attached for your solution please)

I need your expert solution in sending an e- mail message as a reminder to the person concerned before 15 days of due date with an optional text of my choice.

Its not possible for me to open neither the excel sheet nor the ms outlook application on a time basis coz my profile mostly deals with Industrial Relations aspects. Hence I've to be in the field 12/7 during the shift period and on call 24/7.

Awaiting early reverts.

Regards,

SNU.
 

Read other answers
RELEVANCY SCORE 108

HI, Its my first post here. I need to send reminder mails from outlook based on excel reminder dates.
For this i should not open excel neither i know VBA scritping for this.
Please provide help on this.
As i attached my file i need list of assets need to be calibrated ahead of 45 days of calibration due date.
 

Read other answers
RELEVANCY SCORE 106.8

Hi Everyone!

This is my first post here. I need your help in developing a mechanism to send an automated outlook mail, when the due date of a payment is 10 days away from the current date. The script should preferably run automatically every time the PC is running. without the excel file necessarily open.

In the attached excel file, An email should go of to -email address (Col. Q), with subject "Vendor Payment (Col. C) is due on Due date(Col. M)", and body "Dear Name(Col. P), please update the payment status".

Also, the script should put a check mark on Reminder sent column (Col. Q) after the mail is sent, the script should also check if the value of the cell is blank before sending email.

I have scored the forum for similar problems, and although I found a number of threads, I am not proficient enough in VBA to modify them to my needs.

I'd really appreciate any help,
 

A:Automatic Outlook mail due date reminder based on Excel file

Read other 15 answers
RELEVANCY SCORE 106.8

Hello all,

I'm receiving mail alert based on date only, it is not considering the month into the account.. Kindly suggest..

Attached the excel file with macro...
 

A:Automatic Outlook mail due date reminder based on Excel file

Read other 16 answers
RELEVANCY SCORE 106.8

Hi Everyone!

This is my first post here. I need your help in developing a mechanism to send an automated outlook mail, when the due date of a project is 7 days away from the current date. The script should preferably run automatically every time the PC is running. without the excel file necessarily open.

In the attached excel file, An email should go of to -email address (Col. D), with subject "Project (Col. B) is due on Due date(Col. C)", and body "Dear Name(Col. A), please update your project status".

Also, the script should put a check mark on Reminder sent column (Col. E) after the mail is sent, the script should also check if the value of the cell is blank before sending email.

I have scoured the forum for similar problems, and although I found a number of of threads,I am not proficient enough in VBA to modify them to my needs.

I'd really appreciate any help,

Thanks
 

A:Automatic Outlook mail due date reminder based on Excel file

Read other 8 answers
RELEVANCY SCORE 101.6

Hi All,

My name is Diego.

Can anyone send me code to automatically send me an email when the date listed in "column J" is the same date as today. Also, it needs to email only once and even if I am not running excel or at my computer. I want to use Microsoft Outlook and use the ClickYes program as well if this helps that was talked about by Zack Barresse in

http://forums.techguy.org/business-a...s-using-2.html
Essentially I have to be reminded of a reapplication for specific state licensures on healthcare courses I provide. I don't want to forget which courses I have to reapply for so I need to have a program that will look at a date which I have in column J and then email me to remind me of this.

BTW - I am using Outlook 2007 and Excel 2007 on Vista.

Thanks. I appreciate your help! Also, extra points and praise for the person who solves this problem!
 

A:Automatic Email from Excel based on Date in Cell

Read other 16 answers
RELEVANCY SCORE 100.8

Hi All,

My name is Diego.

Can anyone send me code to automatically send me an email when the date listed in "column J" is the same date as today. Also, it needs to email only once and even if I am not running excel or at my computer. I want to use Microsoft Outlook and use the ClickYes program as well if this helps that was talked about by Zack Barresse in

http://forums.techguy.org/business-applications/710581-solved-automatic-email-alerts-using-2.html
Essentially I have to be reminded of a reapplication for specific state licensures on healthcare courses I provide. I don't want to forget which courses I have to reapply for so I need to have a program that will look at a date which I have in column J and then email me to remind me of this.

BTW - I am using Outlook 2007 and Excel 2007 on Vista.

Thanks. I appreciate your help! Also, extra points and praise for the person who solves this problem!
 

A:Automatic Email Reminder from Excel based on Date in Cell

Please do not post duplicate threads.
One thread per issue.
Continue replies for this issue in this thread: http://forums.techguy.org/business-applications/856705-automatic-email-excel-based-date.html
Thank you.

Closing thread.
 

Read other 1 answers
RELEVANCY SCORE 100.4

Hi,

New here. I dug up a thread that Zack Barresse solved many years ago. I am looking to do the exact same thing. The link to the thread is below. My file is infinitely more complicated than what that user was asking for so I need a bit more help tuning the VBA. Link: http://forums.techguy.org/business-applications/710581-automatic-email-alerts-using-excel.html

Some specifics:

- I am using Outlook not Express
- Excel 2007
- All the functionality is complete for monitoring several live streams of securities data with several trade indicators.
- It is consolidated onto one sheet for manual monitoring (Picture below). Basically takes copious amounts of data and reduces it to just IF and AND functionality for the triggers for easy use from all the other sheets.
- The workbook will be open and running/refreshing on its own 24/7 as it is now.

I am a busy guy, I just need the VBA to automatically email me remotely when any of the 7 currency pairs causes a trigger when I am on the go. I can log trades from an app on my phone.

One other hurdle would be that if say (Using percentages to keep it simple) that a trigger would be if something reached as high as 80% to send the notification email. But where the system refreshes every 60 seconds it shouldn't send another notification each time it remains at or above 80%. Just the once. It may remain there for hours and that is a lot of emails.


Thoughts? and many many thanks in advance.
 

A:Excel - Auto Email based on cell value

Read other 10 answers
RELEVANCY SCORE 96.4

I've read the previous post with the same issue, but I'm unable to understand how to use the other codes posted within my product. I would like to send an email based on a date. I will attach my document so it is easier for me to explain the requirement. Columns L37-L45 have due dates - I would like the email to be sent 60 days prior. I have posted some mock emails in R37-R45 and the email message in the EMAIL workbook tab. Any assistance would be greatly appreciated.

Thank you so much!
 

A:Auto send an email based on date in Excel

Welcome to the board.
I've had to save it as 2003 version but the code works under 2007

See attached my copy of your sheet with the code in ThisWorksheet module.

This just a simple way of doing it and you will have to edit it for your needs but maybe it can put you on the right track.
 

Read other 2 answers
RELEVANCY SCORE 94.4

Hello everyone.

I am trying to tweak a macro that was posted on this forum some time back, but I'm a bit of a N00B. The macro is from this thread that is now locked: https://forums.techguy.org/threads/...ue-date-reminder-based-on-excel-file.1129238/. What I am looking to do is generate two different emails whenever the value of a cell changes. All the data that needs to be part of the email is included in various cells. I've included sample data. If possible, could you provide code for generating the email and another for sending the email without opening Outlook (i.e., as soon as the value changes)? I've included sample data for your consideration. Thanks in advance for any help you can provide.

When Cell Value = In Progress
The email I'm hoping to generate (I've mapped it with the cell contents I'm hoping to pull) will be as follows when Column F changes to In Progress. The email addresses are in Column G.

Subject Line: "Web Request(s) Now In Progress"

Dear D2,

Your C2 B2 request has been received and is now in progress. Thank you!

My Signature

When Cell Value = Completed
The email I'm hoping to generate (I've mapped it with the cell contents I'm hoping to pull) will be as follows when Column F changes to Completed. The email addresses are in Column G.

Subject Line: "Web Request(s) Completed"

Dear D2,

Your C2 B2 request is complete and the changes are now live. Thank you!

My Signature
 

Read other answers
RELEVANCY SCORE 92.4

Hi

I use windows 10, Excel 2016-32 bit (part of the MS office 365 kit).

I have an employee database made in excel, it has the date of birth of an employee,
based on the date i would like Excel to pic a random pic from a collections of pic stored in a second sheet of the same workbook, and email it as a HAPPY BIRTHDAY message to the email address of the employee and mark a copy to the organisation.

I use OUTLOOK 2016 ( part of the MS office 365 kit)

I am not a software developer and know not anything about coding.
appreciate all the help from this forum.. and thanks in advance for every feedback received.

Regards
sajumd
 

Read other answers
RELEVANCY SCORE 86

Hello,

I cant seem work out a solution for what I'm trying to do. I have an Excel workbook that has multiple sheets. On sheet 1 i want the data from cell "G3" to be copied onto sheet 2. But i want the location on sheet 2 to be based on whatever was entered into cell "D3" on sheet 1.

For example: Sheet 1, cell D3 I have the name John, in cell G3 i have 68. I want "68" to be pasted in sheet 2 in cell B26.

But if the name in Sheet 1 cell D3 is Suzie, then I want G3 to be pasted in Sheet 2 in cell D26. So I would need to identify the paste location for each person.

I want the data to paste to the next cell so that the next entry can be pasted below the last entry for that person (for John the first entry would go into cell B26, then the next entry would go into cell B27 and so on).

But i want it to be a specific range, i dont want data to be pasted past 20 cells (cell B45). If possible a message box could be created to let the user know that the max is reached.

I would appreciate anyone's help with this as i have been struggling for awhile to try to get this. Thank you
 

A:Excel - Copy paste cell into range based on another cell

Read other 12 answers
RELEVANCY SCORE 86

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
 
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
 
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers
RELEVANCY SCORE 86

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
 
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
 
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers
RELEVANCY SCORE 82.8

Hi,

i have used the following to get automatic alerts setup when a due date is near:
https://forums.techguy.org/threads/...ue-date-reminder-based-on-excel-file.1129238/

but instead of this looking at the due date column, i would like it to look at a conditonal formatted column, which shows status as either "due" and "expired" depending on due date. i would like it send out to all those with "due tag and not "expired"

I have name in column A, details in column B, emails address in column C, status in column i, and reminder in column k
Somethng else i would like to do later on, if at all possible,is to get a particluar email from outlook be transfered to excel and and then emailed to specific people based on a status column. So if an email is recieved to say a project is extended or cancelled,, it contents is copied to excel and emailed back out to certain people.
Thank you for your support.
 

Read other answers
RELEVANCY SCORE 82.8

Hello Forum member,

I am working on an excel file with hundreds of records and tens of columns. There is a column"L" which gives the date when the license key given in column "B" of a particular software is going to expire. I need to check the column and send email to Person X [email protected] 3 months in advance so that he can take poper action to get the license renewed. It is too much of a manual work every week.

I am looking for a macro that can send an email to person X stating that license key value given in coumn B is due to expiration in 3 months from today(based on value given in column L)

I have seem a similar thread in the forum giveb below but i need changes to it. Unfortunately, the thread is closed and i can not contact the person who gave the solution. I am not able to modify the code shared to match my requirement.
Automatic Outlook mail due date reminder based on Excel file

http://forums.techguy.org/business-applications/1129238-automatic-outlook-mail-due-date.html
Can anyone please modify the code given in the above thread to match my requirement. Please!!Thank you!
 

A:Automatic Outlook mail based on a column value in excel file

Read other 8 answers
RELEVANCY SCORE 82.8

I have a sheet with several hundred rows. Starting in Row 4 through the end of the sheet are mixed dates in column 1 and ALL rows from Row 4 through the end of the sheet hidden. I want to be able to type in a date in Cell A1 and have all rows that have that date in Column 1, Row 4 through the end of the sheet to become unhidden. Secondly, I would like to type some other code like "999" or something in Cell A1 to have ALL rows become unhidden. Thirdly, I would like to either type another code or just delete anything in Cell A1 and have ALL rows from Row 4 through the end of the sheet to become hidden again.
Tech Support Guy System Info Utility version 1.0.0.4
OS Version: Microsoft Windows 7 Professional, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM)2 Duo CPU T6600 @ 2.20GHz, Intel64 Family 6 Model 23 Stepping 10
Processor Count: 2
RAM: 3963 Mb
Graphics Card: Mobile Intel(R) 4 Series Express Chipset Family, 1853 Mb
Hard Drives: C: 453 GB (298 GB Free); F: 931 GB (710 GB Free);
Motherboard: TOSHIBA, Portable PC
Antivirus: Webroot SecureAnywhere, Enabled and Updated
 

Read other answers
RELEVANCY SCORE 81.6

I'm working on a spreadsheet that lists monthly recurring expenses. I have one cell on the sheet listing the current date (using the "=Now()" formula), but I'd like to have the other cells change at certain times throughout the month.

For example, if one cell currently says "April-15", when the actual date reads "April-16", the respective cell would change to "June-15". This way, whenever the spreadsheet is viewed the next recurring payment date will be seen.

I know this is the wrong tool for something like this - I'm using SharePoint at the office with a calendar list with workflows that shoot off emails in a much better put together format - but this is just for home use and I'd like to keep it all in one document if possible.

Any ideas or suggestions would be greatly appreciated. Thanks in advance.
 

A:Excel 2010: Cell Date Change with Current Date

The way I read it was that you have fixed recurring payment dates, so you just want to know the next date based on the day today. Hope the attached might assist.
You need to list the payment dates somewhere and then the formula looks up the next date. You might want to look up today plus 1 (or something) to reflect the fact that if the next payment date is today, there's a good chance you'll not get any unplanned payments on that run.
 

Read other 1 answers
RELEVANCY SCORE 80

Hi all,

I know there has been a lot on this subject (so apologies) but as my knowledge in this area is limited and as I can't find anything that matches my exact needs I am struggling.

Basically, as part of my job I manage records in the office but I sometimes forget when things needs renewing etc.

What I would like is a for an e-mail to be sent through and to my outlook when a date has been reached.

Please see the attached: CSCS.xlsx

If CSCS tests had to be done every year for example, Paul Smith would be due another on 01/01/2014. I would like to set up a code so that when it got 2 weeks before that time, I got an e-mail saying 'CSCS Test due' and 'Smith' (i.e.. cell C1 and Column A for reliant person, in this case A3).

Is this something that is do-able and easy to set up?

Thanks, charli
 

A:Solved: Excel Send E-mail (via Outlook) When Date Reached

Read other 10 answers
RELEVANCY SCORE 77.6

Am looking for a way to automatically delete files based on date (files that are 2 days or older) on a NT4.0 server. I'd guess there's some way to do this with a batch file, just not having very good luck figuring it out.
Thanks
 

A:Auto Delete Files based on date

Well i think you are posting it in the wrong section it should be under development. I am also curious on this to, So if you find out how send me a PM.

I am also wondering if you could do this with a script? Maybe a VBS script or somthing that will just do what you stated above removed any NT4.0 File thats Older than two days.
 

Read other 2 answers
RELEVANCY SCORE 77.2

While using XP, I used Pegasus mail as my e-mail client. Pegasus's mail filtering options included the ability to set a duration on a mail folder such that any message in that folder, older than the specified duration, was automatically moved to the deleted mail folder.

Is there any way to achieve the same thing using Windows Mail under Vista?

Thanks

Tony

Read other answers
RELEVANCY SCORE 77.2

Sounds like a simple thing but I’m having troubles finding an easy answer. Still searching ….

I want to create a catalogue for a customer of mine (basic – but might be more extensive later)

– showing a thumbnail picture and then a short product description. Then he can go into the doc and correct the descriptions etc.

Not sure if excel or word or other is the best place.

He has over 200 product pictures stored on disks and folders on hard drive. I have corresponding description in excel based on a code that is the photo name (+.jpg).

So, I have the product files name as HB1000.jpg and all I want to do is show the picture (say at size 100x100)

Ideally I’d like to build a reference based on the code and not have to insert each file.

Thoughts??

So far – tried creating a formula/reference in excel with “Code” HB1000 (Cell A2) and image cell contains “=c:\productimage\”$A2$”.jpg” - can not get the pic to display
 

A:How to display images in Excel based on cell ref

The answer is already on here, I posted it fairly recently, it requires VBA to do it well.
see this thread
http://forums.techguy.org/business-applications/723855-solved-insert-image-based-cell.html
 

Read other 2 answers
RELEVANCY SCORE 77.2

Hi all,

I have a workbook containing 30 worksheets. The first sheet is named Master, while the other 29 worksheets are named with cities where we held branch offices (Los Angeles, New York, Chicago, Washington D.C., etc.)

I am trying to write a macro that will copy the contents of a row in the Master sheet to a particular worksheet based on the contents of the cell in that row that contain the location.

To illustrate:
Master sheet:
Columns are: Staff Name, Location, Project status

We have over 5000 staff.
I am trying to write a macro that will copy each staff's record to the worksheet corresponding to his/her location, depending on their project status.

So I am planning to use macro buttons labelled A, B or C (these are the values in the Project status column). So when I click on the button labelled A, the macro will go through all 5000 employees, and for those employees whole project status is "A", will copy that employee's record into a worksheet depending on his/her location.

For example:
Staff name, Location, Project Status
Joe Doe, Los Angeles, A
Jane Davis, New York, A
Mike Smith, Los Angeles, C

When I click on the macro for "A", it should copy Joe Doe's record into the worksheet labelled Los Angeles and copy Jane Davis' record into the worksheet labelled New York.

Thank you so much for your help. I've been searching all over the place for the command to use to reference the worksheet.
 

A:Excel - how to reference a worksheet based on value of a cell

For the love of PacMan, make sure you save your file BEFORE you try this. Try it on a copy of the file, even.

HTH,
Andy

Sub A_Status()
'select the list of staff names on Master sheet, then run
For Each Cell In Selection
If Cell.Offset(0, 2).Value = "A" Then
DestinationSheet = Cell.Offset(0, 1)
Cell.Resize(1, 3).Copy
Sheets(DestinationSheet).Select
'assumes each location sheet has a header row
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Master").Select
End If
Next Cell
Application.CutCopyMode = xlCopy
End Sub
 

Read other 2 answers
RELEVANCY SCORE 77.2

Hey all:

I'm have really no experience with coding, so I'm out of my depth trying to do this myself. Hopefully someone can help, so here goes!

Essentially, my excel sheet keeps track of dates of when files need to be renewed. They need to be renewed every 60 days. So I have an "IF" formula that tells me if it's time for renewal.

What do I need to do to get an email notification whenever a file comes up for renewal? I've attached a sample sheet.

Best,
Nick

PS.
Using Excel/Outlook 2010
ClickYes Pro is installed (don't know if it will be necessary or not)
 

A:Solved: Emailing from Excel Based off Cell Value

Read other 16 answers
RELEVANCY SCORE 76.4

I am looking to set up a macro or formula that basically inserts the current time when a cell is filled SO:

when a2:a100 is populated with any thing then the corresponding C cell will be populated with the time.

AND

when e2:e1000 is populated wiith anything then the corresponding D Cell will be populated.

Currently I am using this macro.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A6:A100")) Is Nothing Then
With Target(1, 4)
.Value = Time
.EntireColumn.AutoFit
End With
End If
End Sub

which works great for the A and C cells

but it does not work for the other one.. how do i add and modify this so all my needs are met.
I need another 2 sets of 2 macros accross the sheet for time entry.
 

A:Excel date auto populate date/time

Read other 11 answers
RELEVANCY SCORE 76.4

In order for my wife to get reimbursed, my wife has to turn in a Mileage Log every 2 weeks. In her Mileage Log form, each cell in the "Starting Location" (Column B) and each cell in the "Destination" (Column C) has a list of addresses from which she can select. The lists are generated from the DATA Sheet mileage chart

As an example and in other words, if we select the address at 417 Victoria Dr., Kerrville in Column B and select the address at 110 Ash, Ingram in Column C. By looking at the mileage chart on the DATA Sheet, we see the distance between those 2 places is 10.6 miles. Presently, we have to enter that mileage manually in Column F.

Is there a way to have excel get the mileage number from the DATA sheet based on the locations selected on the Mileage Log sheet. I could probably use a lot of nested IF statements but that would be real cumbersome and very hard to modify as she has clients come and go from time to time in which case I would have modify each IF statement. I'm sure there is a better and simpler way.

Tech Support Guy System Info Utility version 1.0.0.4
OS Version: Microsoft Windows 8.1, 64 bit
Processor: Intel(R) Core(TM) i7-4790S CPU @ 3.20GHz, Intel64 Family 6 Model 60 Stepping 3
Processor Count: 8
RAM: 12227 Mb
Graphics Card: NVIDIA GeForce GTX 750, 1024 Mb
Hard Drives: C: 1848 GB (1769 GB Free); D: 2794 GB (2536 GB Free);
Motherboard: ASUSTeK COMPUTER INC., K30AD_M31AD_M51AD_M32AD
Antivirus: Webroot SecureAnywhere, Enabled and... Read more

Read other answers
RELEVANCY SCORE 76.4

I have some excel knowledge but am not too proficient in VBA. I am looking to generate emails based on the any of the conditions I have specified below for column G "Current Approved to Date"

Conditions:
1. =AND(($G9<Today()),($I9=""))
2. =AND(((J9-G9>3)),($I9="Applied for Ext."))
3. =AND((J9>G9),($I9="Active"))

If any of these conditions are met I would like an email to be generated.
(SIMILAR VBA) https://forums.techguy.org/threads/solved-automatic-email-alerts-using-excel.710581/page-2
 

A:Excel Generate emails based on conditions of cell.

Read other 10 answers
RELEVANCY SCORE 76.4

Hello

I need to do the following in VBA:

If cell in column C contains the value GBP, change cell S from that row from 13 to 23
or
If a cell column C contains the value USD change cell S from that row from 13 to 33

exception cell: C1 --> contains the title of the column

How do I do this?
 

A:Excel 2003 replace macro based on value in other cell

Read other 6 answers
RELEVANCY SCORE 76.4

Hey all,

I'm new and have no experience with Excel vba coding. Hopefully someone here can help me out.

My excel sheet keeps a list of Email addresses on column B (with duplicate email addresses), and their particulars from column C (Item price, purchase date, etc) onwards.

I need the vba to email multiple recipients (those with the "notification" field marked as yes) with their purchasing details in it. It should also prevent multiple emails to the same email address.

Thanks in advance,
Leon

PS.
Using Excel/Outlook 2010
 

A:Emailing multiple recipients from Excel Based off Cell Value

Read other 9 answers
RELEVANCY SCORE 76.4

Hi guys,
I need to write a macro that is connected to many buttons, one for each row in spreadsheet, ask for a value to subtract, error check and (if ok) subtract from a corresponding value in column P.

When the button on a left hand side is clicked:
a dialog button should pop-up prompting a user to input the number of pieces to subtract from the corresponding cell (row based on the row of the button, column P)
error check the input number (show error if the value is smaller than 0 or larger than the corresponding value) ... I can write the error check by myself, so no need to do this step
subtract the number from the corresponding cell (i.e. user clicked a button located in cell A9, input 9,24 pieces in a dialog box and there are 15,97 pieces in P9 => the pieces in P9 should change to 6,73)
Thank you for helping me out!
Hope, the description is not very confusing.

Michal
 

A:Excel VBA: Subtract from a corresponding cell based on button placement

Read other 9 answers
RELEVANCY SCORE 76.4

Hy guys

2nd time i am posting stuff for help, and as i was helped before i will again look forward the response.

I have a file of excel, in which i am sending emails to different candidates of admission, with scan letter placed in the same folder by name.

I want to edit this code, which could select attachment based on Column A list adjacent to the email address

I am attaching the file also pasting the code

Sub Test1()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Working in Office 2000-2013
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim SigString As String
Dim Signature As String
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo cleanup
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "C").Value) = "yes" Then

Set OutMail = OutApp.CreateItem(0)

strbody = "We at Graduate School of Engineering Sciences and Information Technology are extremely pleased to know that you have selected Hamdard University as preferred choice for your graduate/post-graduate Studies. " & vbNewLine & vbNewLine & _
"Hamdard University is a pioneer Higher Education Institute (HEI) of Karachi producing Masters and PhDs in the fields of Engineering, Computer Sciences, Information Technology, Energy and Environment since 19... Read more

A:Attachment based on cell value in a excel email macro

anybody ???
 

Read other 2 answers
RELEVANCY SCORE 76.4

Hi all, long time reader, first time poster. I searched around and have found similar queries to what I require however I haven't been able to adapt these solutions with any success so here we go....

I have a list of businesses in a spreadsheet with information such as name, phone number, e-mail, website, postcode etc. I would like a search box on the side, say in cell O2 where a user can input part or all of a postcode e.g. (LS19 or LS) and those that do not begin with LS would be hidden. The postcodes are in column G.

I'm trying to make this as simple and user-friendly as possible so employees can find businesses in a certain area with ease. Thanks in advance for any help and please feel free to ask questions.

Chris Needham
 

A:Excel VBA : Hide rows based on cell input.

Seems nobody had the answer so I found a work around myself. Just in case anybody else wants to do a similar thing. Using auto filter is the easiest way.
Code:

[SIZE="3"]Sub Search()
Columns("G:G").Select
Selection.AutoFilter
ActiveSheet.Range("$G$1:$G$999").AutoFilter Field:=1, Criteria1:="*" & Range("O2").text & "*", _
Operator:=xlAnd
End Sub[/SIZE]

Where G is the column to search for and O2 is the users input data.

Guess this can be closed now, thankyou all for your help.
 

Read other 1 answers
RELEVANCY SCORE 76.4

Hi All,

I need to make a macro that will add borders to certain cells.
I have a generated report from another product so I don't know how many rows there will be until the report is generated. The number of rows is specified inside the report, so I do have access to it. It is stored in cell C5.

So what I want to do is add borders around C5 number of rows starting at A7..E7

I tried using a rule, to just put borders around cells that contain data, but that won't work because sometimes cells are blank and I still want the border around them.

Help?! Please

Thanks a bunch!!!
Lori
 

A:Excel Macro to add borders based on cell contents?

Hi Lori

Here is a macro that will take the value in C5 e.g. 10 and then apply borders to all cells in A7 to E16
Code:
Sub add_borders()
lRow = Range("C5") + 6
Range("A7:E" & lRow).Select
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlC9ontinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End Sub
 

Read other 1 answers
RELEVANCY SCORE 75.6

I would like to know a method to solve an otherwise repetative task in Excel for me.

In my Excel spreadsheet, I have many columns relating to customer orders etc. Each customer (finite number (~40)) has its name (customer1, and customer2 etc) input to a cell, I then must fill out the customer account number (ac001, ac002 etc). The customer name and number already exist on another sheet.

How can I automate the process of when I input the customer name, excel then fills in the customer number in the next cell?

Many thanks
Dave
 

A:Excel: Conditional Cell Input Based on Entered Data

Depending on how your Customer data is arranged, look at either Dlookup or Vlookup.
But Access is better for Customer databases.
 

Read other 3 answers
RELEVANCY SCORE 75.6

I am using Excel 2010

I want to limit a validation list based on the contents of Cell D4
There are 4 lists possible based on the contents of D4:

If cell is 1 = list (on datasheet tab) should be limited to range A42:A71 only

If cell is 2 = list (on datasheet tab) should be limited to range A72:A87 only

If cell is 3 = list (on datasheet tab) should be limited to range A88:A90 only

If cell is 4 = list (on datasheet tab) should be limited to range A92:A93 only
Thanks
 

A:Excel 2010: Limit validation list based on another cell

Soooo... is this possible or no?
 

Read other 3 answers
RELEVANCY SCORE 75.6

Hi,
I am having data structure as below. I need some macro or some means to populate formula in Column D based on column C (as and when it changes) . All other column data are populated. This is beyond me. Any help or direction is greatly appreciated. Thanks in advance

Col | A | B | C | D | E
------------------------------------------------------------------------
Row | 1 | Test1 | | | 10
| 2 | Test2 | =A1 | =MAX(E2) }| 15
| 3 | Test3 | =A2 | =MAX(E3) | 20
| 4 | Test3 | =A1&","&A3| =MAX (E1,E3)| 30
 

A:Excel macro or formula - change field based on other cell

Read other 7 answers
RELEVANCY SCORE 75.6

Hi folks, not sure if this is possible or not but I'll try to explain what I'd like to do.

On the first sheet, I have a simple sheet to be filled in daily. The date is changed, and then daily pick figures and hours picked are updated by the relevant department.

I then have to manually put all of the data into the second sheet, which acts as a log of previous pick figures.

Is there a way the second sheet can automatically be filled in when the date is changed and new figures are put into sheet 1?

I hope I've explained this well enough, I'm trying to teach myself excel as I'm going along! Thanks.

Edit - I've had to delete peoples names' for obvious reasons, initials in sheet 2 will correspond to people in sheet 1.
 

Read other answers
RELEVANCY SCORE 75.6

I have an excel sheet with two tables for customers to know which products the customer is using based on a Contract table.

The enclose sheet has two tables:
1. Customer
2. Contracts

All what I need is to fill the columns of "Product A", "Product B" and "Product C" of every customer looking at the table "Contracts" where the data is available based on the "Customer ID"

How can I do it?
 

A:Solved: MS Excel / Filling a cell with YES based on data from another sheet

I would just use a simple countifs formula to check if the customer ID and Product type match (ie the count of matches is greater than zero)
See attached.
 

Read other 3 answers
RELEVANCY SCORE 75.6

I am working with the attached spreadsheet in Excel 2010 and am trying to figure out how to code certain parameters that will make Excel send myself, my client or other individual an email (with text in body) if certain dates have not been entered into particular cells, or if a cell has exceeded a certain number of days in a particular cell. I have attached a sample spreadsheet and have listed at the bottom 8 points in which I need an email sent, what the trigger is and what the action (email sent to) is.

I just know enough to be very dangerous with Excel but have found that there is a way to code in Excel to send emails which would greatly help my business but I just don't know that much about codes at all.

Can anyone please help me??

Thanks!!
 

A:Excel Coding to Send Email based on Cell Entry

Hi, welcome to the forum.

I suggest you do a find in the forum, there are many posts that gao about this and there are many answers, I'm sure there is one that will help yu and of course one of us can help you if you're still stuck
 

Read other 2 answers
RELEVANCY SCORE 75.6

i have a spreadsheet that updates with data from a number of different sources. i need to filter only the relevant data that i need. i'm almost there but need to find out how to pull 2 more pieces.

in the attached spreadsheet the cells i need are in column R and T. I need to know the corresponding "name" and "size" of the data that is in column S.

unfortnuately the actual values don't show up in the spreadsheet b/c they are live data feeds but the formulas written up until now are still there.

in a nutshell what i'm doing is getting 5 price feeds and i need to filter out which is the highest updated within the last 2 minutes. i actually have the only the price figured out in column S, but i also now which name and how big the size is related to that price.

your help is very appreciated.

regards,
 

A:Solved: excel - data returned based on the value of a specific cell

Read other 10 answers
RELEVANCY SCORE 75.2

I am trying to create a code that will highlight an entire row the day before the date entered into a cell on that row.
 

A:Excel/Highlight Row Based on Date

use conditional format
add a formula

=cell with the date = today()+1
that will be true if the date

just to make sure we understand

today() is 7th August
the date in the cell is 8th august
so you want it highlighted

so cell = 8th
today + 1 = 8th
it highlights

now to highlight the row
use a $ on the cell that has the date and apply to the whole row

so if the date is in column C and say row 5

then
$C5 = today()+1

in the conditional format formula and apply to
$A$5:$Z$5
or whatever column you need

a sample spreadsheet would be useful , you can attach here
 

Read other 1 answers
RELEVANCY SCORE 74.8

There is a file shared by my group where we track work orders scheduled by date. Column 1 contains a list of dates, in order, like so
5/30/06
5/31/06
6/01/06
etc.
Sometimes, when you open the file, someone else has scrolled up or down the page, so if you want to view today's date, you have to scroll up or down to find it. What I would like to do is create a macro that takes you to the cell containing today's date. I tried creating a macro that did the following:
1- Go to a cell containing a formula =TODAY()
2- Copy the value of that cell (this will be today's date)
3- Open the Edit --> Find window
4- Paste in the value I just copied
5- Click find

This worked great on the day that I created it, but today, it still goes to yesterday's date. When I look at the macro, what it's doing is always searching for the value that "TODAY" had on the date I created the macro. It's not smart enough to actually paste in the value I copied, but instead sees it as FIND: WHAT="5/30/2006"

Is there a way to use the Find or CTRL-G, or any other formula to take me to the cell containing today's date? It seems like this should be a simple thing, but I just can't figure out a way to make it work.

Thanks for any help you can offer!
 

A:Excel: Go to cell containing today's date

If you have a cell on the worksheet with =Today() in it, that cell will always show the current date. You look up that in your macro and use that to find your current date in the data.
 

Read other 3 answers
RELEVANCY SCORE 74.8

I need a code that will allow the workbook to be emailed when Column A is populated by certian numbers. The numbers in column A corespond to particular email addreses. This is the code I've been working but it isn't functional.

Sub Email_Out()
If Worksheets("Sheet1").Range("A5:A200") = "190030001" Then
ActiveWorkbook.SendMail Recipients:=("[email protected]")
ElseIf Worksheets("Sheet1").Range("A5:A200") = "190450025" Then
ActiveWorkbook.SendMail Recipients:=("[email protected]")
End If
End Sub

All help is greatly appreciated!
Mikey
 

A:Solved: VBA email excel workbook based on cell values using; If Then ElseIf Please he

Read other 16 answers
RELEVANCY SCORE 74.8

Good Afternoon - this is a follow-up to an earlier post that has been closed.

http://forums.techguy.org/business-applications/1090938-emailing-multiple-recipients-excel-based.html

I would like to do something similar.

My Excel sheet has a list of Email addresses in Column A (with duplicate email addresses).
I have several other columns with data that that I would like to have appear in the body of the email in Outlook.

I need to collate each row with the same email address so ONLY 1 email is sent to each recipient.

Is this something easy to do?
I have little to no VBA coding skills

Attached is an Excel mockup of what I am attempting to accomplish.

The 1st tab called "Sample Data" is basically the raw data I want to leverage.
(which I also tried to display below)
Email Address .....Invoice Number .....Date..... .....Dollars
​ [email protected] .............1 ...............7/3/2013 ......$10,000
​ [email protected] ..............2 ...............7/9/2013...... $50,000

[email protected] ..........3 ...............7/9/2013 ......$40,000

[email protected] ............4 ...............7/10/2013 .....$1,000

[email protected] ............5 ...............7/11/2013 .....$3,000
​The 2nd tab called "Body of Email" is an example around how I would like to see the data appear in the email.
Even though [email protected] appears 3 times in the above example, I ONLY want him to receive 1 email that contains 3... Read more

A:Emailing multiple recipients from Excel Based off Cell Value Collate to one email

Read other 8 answers