I have attached a sample workbook. The workbook already has a macro that can send an email after working out if the date is overdue. I have put a button on the screen, but I would like to remove the button and automate it, I got the initial macro from,( whoever initially put it together) but I have since made some changes to it regarding how the message displays etc. i, 15 or i, 16 in the macro means, There isn't anything in column i and I can't work out the relevance of it.
I got this VBA code from a colleague which he uses when sending multiple emails from Outlook. This code sends email based from the last cell. What I would like to do is send emails to all the recipients listed in column C. Column A will be the sender and column B will be email subject. Attached is the sample file.
1. Attached is an excel spreadsheet. In column A, each time the word total is seen, i want an email to be sent to to someone with the total in colum E.
Eg. A B C D E UAFEQ1 TOTAL xxxx xxxxxxxx xxx R 15 000 ALPROP TOTAL xxxx xxxxxxxxx xxx -R 20 000
For UAFEQ1 TOTAL I would like a mail to be sent to john@example.com For ALRPOP TOTAL I want like a mail to be sent to peter@example.com
If the total in column E is positive i want the mail to say: Please see deposit of R 15 000
If the total in colum E is negative i want the mail to say: Please see withdrawal of -R 20 000
The currency is Rands (South Africa)
Sometimes, i need to send 20 or more e-mails. And these can repeat themselves so, there may be two totals for UAFEQ1 TOTAL and a mail must be sent each time. See attached as an example.
The macro can stop when Grand Total is reached. No email needs to be sent for the grand total.
I have a spreadsheet showing MOT details of vehicles. The details include 'Customer', Vehicle Reg', 'MOT due date', 'email address'.
Basically.. I want a method that will check whether or not the MOT due date is in 28 days time (so exactly 28 days from now).. and if it is.. then I want to use the email address that is on the same row to send the person a standard notification.. and if possible.. include details of the Vehicle Reg.
I am working on a spreadsheet that keeps track of upcoming contract and service renewals. Within the spreadsheet there is a column named "Status"; using a formula based on the renewal and engagement dates this column will automatically state one of the following -
"Contract Status OK" "Up for Renewal" "Renewal Due"
When I open the the file I would like excel to email 2 specific email addresses. The email should only contain data from the rows that state "up for renewal"; this email will act as trigger for my team to engage with the suppliers in question.
How do I send a text file to a usb dot matrix printer as opposed to a parallel port.
I have an Excel spreadsheet that prints labels on a dot matrix printer. Until now the printer used a parallel port. My Excel workbook generates a text file "C:PRINTLABEL.TXT". I then execute a shell command to run a batch file called
"C:PRINTLABEL.BAT". The BAT file contains the line ---------------------- copy C:PRINTLABEL.txt lpt3 ------------------------ The shell command contains the lines (not all lines are shown) ------------------- On Error GoTo ErrorHandler ' Enable error-handling routine. RetVal = Shell("C:PRINTLABEL.BAT") ' PRINTS LABEL. Exit Sub ' Exit to avoid handler. ----------------------------------------
Now I have to deal with a newer dot-matrix printer (EpsonLX300+II) that is able to use a USB port.
How do I print the plain txt file to the printer using the USB port (LPT1/2/3 are all parallell ports, so the batch file does not work as it stands.
get the code for sending multiple emails as per the spreadsheet list. Assume the spreadsheet has 100 line items and each columns specifies the name of the person, value, recipient email address ("To" and "CC") and sender name.
And the Body of text is :
Hi "Name of the person" Please find the value of "Value" to be paid for the moth of xxx and kindly let me know for further clarification.
I am trying to send emails using a table in Xl2002 via outlook2002.
The code below is from Walkenbach's VBA Bible for Excel 2003 and I am getting "Uesr-type not defined" error with the 1st Dim statement.
Do I need a different statement such as set OutObj = blahblah or similar?? I have searche dthe net hi & lo for a solution to this problem & I do have JW's excel VBA bible.
Sub SendEmail() 'Uses early binding 'Requires a reference to the Outlook Object Library Dim OutlookApp As Outlook.Application Dim MItem As Outlook.MailItem Dim cell As Range Dim Subj As String Dim EmailAddr As String Dim Recipient As String Dim Bonus As String Dim Msg As String
This excel includes some text data, in each cell there is an email address along with additional text. I only want to get the email addresses from the entire data.
I've searched the forum and believe this thread to be the closest to what im looking for, but its doesnt completely apply: matrix to list conversion. I have an attachment to support my questions (see attached). I have a matrix of words (strings) that contain repeating and non-repeating contents. The matrix also has spaces which are of no value. Essentially, the VBA needs to ' analyze' this matrix and create a consolidated frequency list (as shown in the file).
The matrices are HUGE and therefore some of the clumsy VBA i am using is turning out to be a bit inefficient. The file also contains formulas and such that I cannot use AutoFilter nor can I use Insert or Delete rows...so the VBA shouldnt use those either.
The above is sample data and the rows will change depending on data. Based on email id the range should be picked up and the email should be send as a attchment.
Code: A B C 20 100monkeys 1 1 10avenue 0 1 10avenue 0 2 10avenue 0 1 10avenue 0 1 10avenue 0
[code]....
I need to write a function, that outputs "1" in C for only biggest number in A, if it is above or equal 20 for each list of values in B. Is this posseble with Matrix-Functions or should I look into VBA?
I have adapted the use of StephenR's solution to a problem similar to that in the thread linked below. However I want to load the data directly into a new sheet instead of loading it into line 27 of the current sheet. This is the code I am working with, I think I need to change r2 somehow, not sure what to do exactly though.
[URL]
VB: Sub KonverterFraMatrixTilListe() Dim rng As Range, c As Long, r As Long, r2 As Long r2 = 27
[Code]....
Another thing that I really want is for it to run not only for rows 4-8, but for rows 4-last row as defined by the A column.
User# User_Christian User_Surname Role 1 bob smith operator 1 bob smith supervisor 1 bob smith warehouseman 2 john stone operator 2 john stone gunman 5 george brown Foreman
What Im after is:- a matrix with unique Role list across the top Unique ID & Name down the side (I can do all the above using recorded macro's) but the clever bit I cant even start to work out is how do I get matrix populated from the list?
I have been using Excel to record the routine daily issue of items to different groups in a matrix layout, I use a different workbook for each month with worksheets for each group. The matrix takes the form of the item issued being the left hand column and the date issued the top row of the matrix, the quantity issued is recorded at the intersection. Each item can have a different quantity issued on different days. I'm using Excel 2011 for Mac but could use PC Excel 2010. Is there a way to convert the data held in this way to a list? What I'd like to achieve is a list showing the Item, the Quantities Issued and the the Issue dates
Using VBA, I wish to work out the inverse matrix of a large matrix (100*100), but keep getting the # Num! Error. I am using the minverse function. I have defined variable as "variant", does this give me the same possiblities in terms of number size as the variable "Double"?
I have created the following attachment to illustrate my problem. I have a square matrix -- say a 10x10 matrix (with data in B2:K11, and column headings on row 1, row headings on column A). I would like to get the sum of products of two columns in the result section below -- cells B14:K23, based on the position of the formula.
For example, cell B14 is the 1,1 item of the matrix so I would like to get the sumproduct of B2:B11 and B2:B11 (or SUMSQ(B2:B11) in this case); alternatively cell I16 is element 8,3 in the matrix so I need to get sumproduct of I4:I11 and D4:D11. I tried the following formula using the OFFSET function (for cell B14 which is then copy/pasted throughout B14:K23)
I'm trying to write a formula that will return the sum of multiple cells within a larger matrix, based on a number that is manually inputted into a separate table. As you can see in the top below, I have data that is grouped by both "Zone" and by individual "Stations". The Zones/Stations on the y-axis are "Start" points, while the Zones/Stations on the x-axis are "Finish" points. What I'd like to be able to do is input a Zone ID number into the bottom table (Shown as "1" below) and have the formula either return the greatest sum of "Finish" numbers. In this example, the greatest sum is 12, which is all numbers going from Station 1 and 2 in Zone 1 to Stations 1/2 in Zone 2 (3+2+4+3). In the bottom table, you'll see the "Greatest Finish" as "2", which is Zone 2. And the total from this sum is 12, as described previously. If there is a way to return either the "Total #" or "Greatest Finish" zone.
The actual matrix I am working with is much larger (150x150), and not all zones have the same amount of stations. I'm assuming this might complicate things a bit.
In 'MAIN REPORT' there are thousands of columns of data, of which the important columns are 'Factory No.' and 'Row No' and 'Quantity'.
Each factory has its own worksheet as well.
In each of those factory sheets there is a 15x65 grid for each of the 65 rows that are mentioned in MAIN REPORT.
I have created and IF statement that checks if there is any data in 'Quantity row' and if there is it enters a "1" in the that cell of the virtual Factory grid.
This is the formula: =IF('MAIN REPORT'!I2="","",1) However, this simply checks that data while there is only 1 quantity for each row of each house.
Sometimes there will be different quantities (for diff. products), and I need a formula/macro that checks the line no. column and then,
If there are 5 items in row 7 for example then it will place five "1"'s in the first five cells of row seven in the corresponding factory sheet.
wish to write an VB macro in Excel (2003) that populates a 6 row by 10 column matrix (for a 3 D graph) by inserting values from matrix row column headers in cells in another worksheet and using goal seek to derive a value which will be pasted in the respective cell in the 6X10 matrix
Specific example: Matrix (Row Headers A2 to A7, Column Headers B1 to K1, Cell values are in B2 to K7). Please note row and column headers are numerical values since this is for a 3D graph
Macro to be written
Pick a value (numerical) from row header A3 in Sheet A and paste it in cell B6 in another sheet (Sheet B). Pick anothet value (numerical) from column header D1 in Sheet A and paste it in cell B7 in Sheet B.
Run a goal seek scenario such that cell S10 in sheet B is equal to 50 by changing cell G8 in Sheet B
Copy the value chosen/optimised by Goal Seek from cell G8 in Sheet B and paste it in Cell D3 (which is the relevant cell for the values for intersection of headers A3 and D1) in Sheet A
Macro should populate all cells in the matrix in Sheet A (matrix cells to be populated are B2 to K7) in a similar fashion
The macro should be locked in to the cells which are either being copied from or pasted into so that it still references them even if new columns or rows are inserted.
I have a range of expiry dates in column F of a sheet with the description of the item expired in column B. Is there a nice little Macro which would ideally send an e-mail out to certain adressees to warn of the expiry or just have a report which is printable showing items which have expired. I would ideally like the report to be generated automatically.
