it is possible to set up a macro that can route a file to specific people based on conditions in the file? what i am trying to do is route via email a excell file on dollar amount. So if file is for $10,000 ; send it to 2 specific people. If its for $90,000 send to specific 4 people.
View 4 Replies
I'm trying to email multiple worksheets from a list to multiple recipients in a list, but in one file per person. In a sheet called "Email list", for example, I have a list of 50 worksheet names (e.g. one for each cost centre) in column A (with a heading in cell A1, if that's OK, so the list starts in A2) and in columns B-F up to 5 recipients for each worksheet (e.g. 3 recipients might be 2 supervisors and their manager).
Also, each manager and supervisor might also receive several sheets.
Although columns B-F are presently free-format (they can be anyone's email address, regardless of whether they're a supervisor or manager), it might be easier to split these columns so B,C,D are supervisors emails and E,F are managers, if that makes it any easier creating the collated file of worksheets..?
Unfortunately the recipients might change occasionally so they can not be hard keyed into the macro itself (which would be much easier), but need to be in a table so it's easier for the user to update and maintain - in the past, I've managed to hard key the requirement into a macro which I've maintained (by recording and editing the macro), but I don't know the VB to figure this new request.
Ideally, I am trying to create a macro that can collate the various worksheets each person will receive and send the selected sheets in one file to that recipient, rather than multiple files within one email and/or multiple emails.
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
Auto generating an e-mail that will consolidate duplicate rows for 'column a' in my attachment. The key here isn't necessarily deleting the rows... What i really need is vba code that will enumerate through the table and auto generate an e-mail for each individual. For each individual, i need the code to generate a table in the body of the e-mail & attach the activity for that recipient and the date. The trip up here is when a recipient has two activities associated to their name, and their name shows up multiple times in the data set.
I need the code to
a) identify the duplicate recipient,
b) send out 1 email for that person, and
c) attach each activitiy/date (2 or more) to a table.
Automate the Consolidation of Duplicate Recipients to 1 Email (2).xlsm
So i want to be able to send a single Excel sheet to a recipient, not already pre-defined in the VBA. I am trying to get to where i can click a button/link and a window pops up, asking me to enter recipient's email address. When I enter the address and press- 'Send', the email is sent using Outlook.
View 4 Replies View RelatedI 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]....
I need to send an email when value of timer in column F exceeds 72 hrs. E-mail address of recipient is in column C and 2 fixed addresses are to be put in C.C.
basic outline would be
name(A) work(B) E-Mail(C) Work Started (D) Work Finished(E) Time Elapsed for Work from Start (F)
Can this emails be sent to repeat also after 24 hrs if the timer in F is not stopped (i.e till no entry is put into Column E)?
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" ....
I have a spreadsheet where I want to require certain fields to be completed then I want to have that file auto emailed. I have learned that I do need to have the file saved before sending otherwise the data will not appear in the email, so with this I want to have the file temporarily saved emailed then the temp file deleted.
Here is the code I have so far but it errors on the blue text, I did change the TempFileName from = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") to = [C16] & "_" & [B6] & "_" & [D6]
Private Sub CommandButton1_Click()
If Range("B6").Value = "" Or _
Range("d6").Value = "" Or _
Range("f6").Value = "" Or _
Range("E9").Value = "" Or _
[Code] ......
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.
How I solved my problem of sending out information to more than one email recipient using Lotus notes email and Excel 2003 vb code. I created groups in Lotus Notes for those with more than one email address Then I assigned a string to vaRecipient like so:
.SendTo = vaRecipientThe string was either one email address or a lotus notes group name.
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?
I have attached the file I am working on. I am attempting to create a link to an external file based on the value of cells in column A. Then I would like to simply copy the formula down, lets say in Column B, the rows and as I do the external file reference will change depending on the value within the cell in Column A. I hope that I am making sense.
I am using Windows Vista with Excel 2007. The files will all be within the same file folder, however, there are hundereds of files so I won't be able to open them all for the indirect to work.
on a macro i use to open, update a file and then save it in an archive.
Opening and updating the file is no problem, but i want to save under a dynamic name in a folder structure. This is a reoccurring task, and this way I update the same file each period but save a copy with the current data in an archive under a different name in the right place of a directory (archive).
My idea is to have a hidden cell in this workbook, where I can have the name calculated by simpe excel-formula, i.e. ="Filename_"&WEEK(TODAY())&"_"&YEAR(TODAY())&".xls".
The file should go automatically into an archive-directory, lets say C:data....archive2007 (2008, 2009, etc)
I want to add the last folder to my filename, so my macro knows the first part of the path and has to go look up the actual name it gets in order to be saved.
So I end up with a cell containting the filename: 2007Filename_35_2007.xls
Now I only need the macro which looks up this name, adds it to the hard programmed path:
C:data...archive
so that the file gets saved under as: C:data....archive2007Filename_35_2007.xls
this is how i start:
Workbooks.Open Filename:= _
"C:data....Filename.xls"
' here the file is updated...
Windows("Filename.xls").Activate
ActiveWorkbook.SaveAs Filename:= _
"C:data....archive'(how do i do this?)'", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
I have a Workbook that already has a macro in it that will generate multiple sheets based on certain criteria.
For each of these newly generated sheets (numbered 1-6 in the attached example), I need to be able save each of them to a PDF file based on a unique name contained in a certain cell (in this case, each named is referenced in cell Q1 of each sheet). As such, I should end up with 6 pdfs based on the attached sample files.
The PDFs would ideally need to be landscaped and should be just 1 page per sheet.
In my attached sample workbook, you'll notice that I have a "PracticePrint" macro that doesn't quite get the job done. One other criteria involves not printing any sheet with the characters "1010" in it (my PracticePrint macro is setup to do that, but it fails to do much else).
How would I save a file with the lastest version number after searching for the lastest filename in a folder and adding 1 to the name
ActiveWorkbook.SaveAs FileName:= _
"C:BarkingEMCOUTNOM_DA_" & (Format( Date, ddmmyy)) & "_EDF_BPL_" & V, FileFormat:=xlCSV
where V will be the version number in the format of 001, 002, 003 and so on
I need to email a page from a worksheet to a series of people and am currently using the following
Worksheets("Report").Activate
ActiveWorkbook.Save
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application. ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
however, I also need the worksheet to be email to the person currently running the macro (i.e. when they email it to the others, a copy is sent to thier own inbox too), is there any way in which this can be done?
The user's email address could be formed from data in the spreadsheet, if there is an easier way to do this (i.e. email address is based on a cell in the spreadsheet).
I have numbers in b2 to b5 say
10
22
35
50
I have numbers in c2 to c5 say
1000
1580
3000
45000
I have numbers in cells d1 to kq1 (1 to 300) d1=1, e1=2, f1=3 and so on
What I want to do is:
In D2 through to kq2 it puts the number in to the cell that correspnds to a multiple of the number in b2.
That would put 1000 in cells m2,w2,ag2,aq2, ba2 and so on
In D3 through to kg3 it it puts the number in to the cell that corresponds to a multiple of the number in b3
that would put 1580 in cells y3,au3,bq3,cm3,di3 and so on
I have Sheet with 40 employees who each proposes their work schedule, so I have to give each Employee access to the same sheet and want highlight and unlock only those cells that specific employee can use.
Each employee has to login from a drop-down (sourced from Sheet.Employee Master), so their unique Employee Number is in "A13" of Sheet.LOGIN
Can I identify the ROW number and then use that ROW number in a macro to highlight and unlock specific Range of Cells in Sheet.PROPOSED SCHEDULE?
---where "Sheet.LOGIN("A13") = (the value in the cell Col A:"row" of Sheet.PROPOSED SCHEDULE)
I have attached a scaled down version of the Workbook.
Following code is scaled down-- this is for Employee 02 who appears on ROW 16 of the sheet. (macro is same for each employee, just uses a different row)
Looking for help with sending emails from Excel file using Outlook, at x-date....
View 14 Replies View RelatedI'm uploading a .msg file but i can't seem to go through with the DisplayAsIcon option and i also want to put the Date at the end of the file's name,to somehow track the uploaded files and maybe sort them by date. At the time of the upload moment i see the filesname with the date at the end of the name but after i save the file it overwrites the objects icon the i end up with an envelop icon and the uploaded files name. Here's my code:
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case ActiveCell.Address
Case "$I$29": Run "Email"
[Code] ........
I have an existing macro that convert active sheet to pdf then email it as an attachment thru outlook.
now, what i need is to attach another pdf file in it, so it will now be 2 attachment. here is my codes:
var all_spans = document.getElementsByTagName( 'SPAN' );
for( var i =0,skip =0 ; i [code]......
I would like to email an Excel file without the macros so the end recipients will only see the data and not be confused or excited about the prompt to enable/disable macros.
View 2 Replies View RelatedI have created a spreadsheet that requires the buttons on a customised tab/ribbon in order to use it.
If this is on my machine where I have customised the ribbon it works fine. However I need to email this spreadsheet to another person - but wanted them also to have my customised ribbon. Is there anyway to do this - or would I have to go and customise their ribbon on their machine?
Below is a VBA that sends an email with a range from Excel in the body of the email, and the entire book as an attachment from a temporary file. It works great, except that I need the entire file to close, and it will not because a message pops up that says "Do you want to save file before switching file status". This will not let Excel close. I need to bypass that pop up message. Display Alerts = False did not work. In the bottom of the code below, there is a spot that tries to kill the temporary file, but it does not work until "No" in the pop up message is pressed. Again, I need to bypass that pop up message.
View 2 Replies View Relatedway to generate automated email whenever excel file is updated by any one.
View 14 Replies View RelatedI managed to create the attached macro with help from stuff I've found in the internet. With this code, I am able to save the file and then send an email cia Lotus Notes. I need two things:
(1) The code to attach the file saved into the email that it sends
(2) The code so that the file is protected so that changes are not made by the recipient.
HERE IS THE
ActiveSheet.Copy
ActiveWorkbook.SaveAs filename:="V:TFMCO8 ReportingHistory" & [c22].Value & " " & Format$([c20], "- (YYYY-MM-DD)") & ".xls"
ActiveWorkbook.Close False
Dim Maildb As Object, UserName As String, MailDbName As String
Dim MailDoc As Object, Session As Object
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, _
(Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Session.GetDatabase("", MailDbName)
If Maildb.IsOpen Then
Else: Maildb.OpenMail...........
I'm trying to create an excel macro that can send emails with an attached file per row of my entry in excel I imagine it would be like the screenshot below. I've tried the code in the link below from a macro tutorial website but wasn't able to make it work due to error DataObject is unkown I'm using excel 2013 and lotus notes 8.5 as my email.
View 9 Replies View RelatedI have developed a product that's a bunch of Excel spreadsheets strung together. Real estate and mortgage professionals use the tools to counsel home buyers. We include a pdf converter in case recipients of emailed file attachments don't have Excel.
We provide an 'Export' procedure that calls a custom dll that uses the outgoing mail server that the user [must] setup or our web's mail server as a default. The action attaches the Excel or the PDF file and sends in the background. We use an Excel form for user to write the message and the process saves a copy of the message sent and the file send in an Archives folder.
This procedure is not very user friendly, as it does not offer the user's address book to select a recipient, does not put the message in the user's email client Sent folder...Plus the Archives folder could become quite large...plus it involves a user setup that creates alot of support calls.
But actually the most critical shortcoming has become that ISPs (Comcast and ATT so far) are blocking the use of custom email clients!
Recently my son showed me in QuickBooks their attach file-to-email process. It simply opens the user's default email client with the file attached. I need an app that does that! And it needs to be distributable.
I've got a file that works fine on my computer. When I email it to anyone with 2010 and they open it from their email account (Outlook 2010), the file automatically opens up in 'Protected Mode'. If the user selects "Enable Editing" the user receives 'Run Time error 91: Object variable or with block variable not set'.
If the user closes out the vba error and saves the file to their computer and reopens the file, it works fine.
BTW, it is not a complex macro, it is error out at
ActiveWorkbook.Sheets("Worksheet").Select
