Loop Through A Spreadsheet Insert Into Another Wb Then Email
May 5, 2006
I am trying to create a macro that loops through a spreadsheet, copies data over to another workbook then emails the new document.
I have taken this code from an old macro that I found but cant seem to get it to work.
This is the code
Sub EmailAssessments()
Dim strTempName As String
Dim strForename As String
Dim strSurname As String
Dim strBookingNumber As String
Dim strReportingTo As String
Dim strBookingGivenBy As String
Dim strJobTitle As String
Dim dtmStartDate As Date
Dim strEndDate As String ''Need to use a string as there maybe no end date
Dim strDepartment As String
Dim strTierName As String
Dim r1 As Range ''For loop
Dim intNumberOfRows As Integer ''Count how many rows.........
I am using Excel 2010. I have been given a task at work that can save my team a lot of time if I can solve the problem. Every month, we have a spreadsheet with about 5000 rows that we have to email. In each row, there is a range that we have to email to a specific email. For example, I would have to copy and paste Range A2-R2 in the body of the email, and then email it to whatever email is in cell S2. I would then continue this for the next 5000 lines, making it a possibility that i will be sending 5000 emails manually.
I have been trying to come up with a solution through VBA that would automatically send these emails. My goal is to automatically send the Range A2-R2 to outlook email, then cell S2 into the "To" email address box, and then automatically send it. So far, i have successfully been able to send one row, but cannot figure out how to loop it for the remainder of the rows.
A couple other key points are that I have column headings as well (Range A1-R1). If possible, I want to be able to include the column headings in the email body as well. Example - first email would be range A1-R2. second email would be range A1-R1 and A3-R3, and so forth. The body of the email would also contain a standard script, such as "Please review the information below."
The goal here is to save everyone from having to send 5000+ manual emails. This would be a big boost for my team.
I need to send out an order form (spreadsheet) to 100's of people that need to complete the form and email back to me as an attachment. If I was completing the order form myself I would use the "email" icon that I have pinned to my Quick Access Toolbar (QAT). However, most of the recipients don't even know the Toolbar exists.
Is there a way I can insert an icon / hyperlink in the spreadsheet that does the same thing as the QAT icon. I can insert text to say "click here to email your order" (or similar).
I need to keep it in an excel format and an icon is so much better that asking them to save to their hard-drive and attach to an email, etc.
The QAT icon is exactly what is needed but I need to provide a spreadsheet that works for folk who haven't got the icon.
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:
I have a spreadsheet that contains an email address (Each row will most likley have the same email address but there are occasions when it could be different). Ideally I want a button that when clicked will open up an email, popoulate it with the persons email (that is in a specific column and row), add a subject and some text to the main body of the email and then attach the spreadhseet itself.
I am trying to send as an attachment "sheet1" to a email list in "sheet2" range "A1-A50" I looked at Ron De Bruin examples but didn't find one to email a single sheet from a list.
I looked at examples at Mr. Excel but didn't find it. I apologize if I overlooked it. If I did just point me to it.
Sub Mailer() 'Sheets("BB Email Data").Select pathname = Range("A1").Value 'defines attachment Dim objol As New Outlook.Application Dim objmail As MailItem Set objol = New Outlook.Application Set objmail = objol.CreateItem(olMailItem)
I'm trying to create a macro the saves a report and then opens an email dialogue box populating the recipients from a list. I have the following code so far but the receivers are constant.
[Code] .....
I have attached a simplified example of the list. The list is always sorted by time and date. Not including headers, the list can be from 1 to an unspecified number of users long and users may repeat throughout the list any number of times.
The user name always appears as their email address prefix and the email address will always end in "@example.com".
I am aware that a loop could perform this task but do not know how to employ it in this case as it must not repeat recipients and will also need to populate the EmTo line. Possibly a second loop for this?
Trying to add a macro to generate an approval email with certain data from the spreadsheet within the body of the email. I have the following in place currently to deploy the email:
Sub Sendemail() Dim Email_Subject, Email_Send_To, _ Email_Cc, Email_Body As String Dim Mail_Object, Mail_Single As Variant Email_Subject = "New Account Approval"
[Code] ....
What do I need to add the following cell values into the body of the email, All Sheet 1?
I have two pieces of code that each work, but I am struggling to combine the two.
I started with Ron de Bruin's code to attach multiple files to an email and then found more code that will loop through a folder to attach multiple files to an email.
Essentially, I would like the structure of Ron de Bruin's code, with the ability to have folder paths in the cell range, rather than file names.
Here is the code that works to loop through a folder, but it only works when I have the paths listed in one column, not in a range (e.g. columns C-Z)
Code: Sub Send_Indv_Files() Dim OutApp As Object Dim OutMail As Object
Is there anyway this can be done using a button in the spreadsheet?
For Example.
I send numerous almost identical forms to a colleague and to eliminate subject line typo's (Reference numbers) i would like the Subject line to match a cell in the document itself.
I have experience in working with Excel, but none on using code/macros
I'm especially having trouble with loops. I'm a little dense in that department. Here is what I am trying to do. I open 3 spreadsheets. I want to consolidate them into one of the spreadsheets. The first obstacle I face is that one of the spreadsheets has a name which varies. It is always called either "PLR.xls" or "PendingLoans.xls". Also, it will have a number at the end depending on how many excel books have been opened so far in the day, i.e. "PLR (2).xls" or "PLR (5).xls". At this point, all I want to do is be able to select this file consistently. I currently have something like this (excuse my syntax - its saved at work and like I said, I'm still learning VBA).
I need to insert values from a column in one sheet to another sheet with a Loop mentioned here.
(I have attached a sample workbook for your kind reference.)
IN STATEMENT SHEET, I NEED TO INSERT (IN col F) THE VALUES from Col A of NOS sheet.THE INSERTION SHOULD BE LOOPED AS MENTIONED HERE
i.e. First time, it should be 1 to 10 Second time it should be 2 to 10 and 1 Third time it should be 3 to 10 and 1, 2 Fourth time it should be 4 to 10 and 1,2,3 Fifth time it should be 5 to 10 and 1,2,3,4…. And so on, till the last row with a value in ColA.
THE VALUES IN Col A of NOS Sheet MAY BE CHANGED WHENEVER REQUIRED. There it is 1 to 10, but it may be Alphabets or any other words also. Hence, whatever values in Col A of NOS sheet should be taken for looping.
I have a requirement where I have to add a row with x columns using vba. this is the code I am using now
k = 17 prodetails = .GetFieldValue("Product Details") sSeats = .GetFieldValue("Seats") If isRecord Then While isRecord Sheets("Products").Rows(k).Insert Shift:=xlDown Sheets("Products").Rows(k + 1).Insert Shift:=xlDown Sheets("Products").Rows(k + 2).Insert Shift:=xlDown ThisWorkbook.Sheets("Products"). Cells(k, Prod6Col) = prodetails ThisWorkbook.Sheets("Products").Cells(k, Unit4Col) = sSeats isRecord = .NextRecord() Wend End If
In this code I have declared Prod6Col and Unit4Col as constants. instead I need to use them as variables like in this modified code. However if i use this code, I get an object definition error.
k = 17 m = 20 (20th column) l = 18 (18th column) prodetails = .GetFieldValue("Product Details") sSeats = .GetFieldValue("Seats") If isRecord Then While isRecord
I have a spreadsheet that I need to add new rows everytime I have a new client. I a formula in the N column and a formula in the F column that I would like to automatically insert itself into the new rows.
The problem is this: I have arranged my table so that I can look at clients alphabetically, and according to other date... BUT now it wont let me insert a new row when I have a new client!!!
Is there anyway of keeping it so that I can insert a new row into my table, but preserve all of the formulas that I want automatically put into both the F and N column?
Using excel 2003. Cannot insert command button on a spreadsheet we have. Almost all icons in the control toolbox are unavailable when Im on this specific spreadsheet. Any new documents I open work fine. Is there some kind of protected spreadsheet that wouldnt allow for a button, picture, textbox label to be inserted??? I didnt make the original sheet but am just editing and improving it.
I would really appreciate any advice you could give, I have attempted to attach a jpg showing the greyed out icons in my controltool box
how to create a loop that grabs the ( t'th ) cell and prints into columns on the spreadsheet adjacently that cell and the ones before it (total = t) and repeats it consecutively until there are no more cells to transcribe into columns. For example - I have n=100 cells in a column that need to be placed into n-t columns (where t = 5 for example).
See image below for clarification, code to follow:
As you can see I need it to fill out in the yellow highlighted cells the rest of that pattern. Right now it only does it for one column, but every time I try to get it to do the rest of the columns, it prints the same first column over and over again (I'm having some severe mental block with implementing the correct loop code).
For clarification also, the user selects the t'th cell and it prints that cell and the previous (3 in this case)... and then it shifts down one cell and prints those 4 cells into the next column - which is what I want it to do.
Sub populate_range() Application.ScreenUpdating = False Dim rowoffset As Integer Dim offsetnumber As Integer rowoffset = Range("F2") offsetnumber = Range("F2") Range("d5:iv5").Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents Sheet1.Select Range("b4").Select ActiveCell.Offset([offsetnumber], 0).Select Range(ActiveCell, ActiveCell.Offset(-([offsetnumber] - 1), 0)).Select Selection.Copy............................
I built a Microsoft Access database for my church to track member information. I was able to import a lot of the needed information to create the records for each individual and household easily. What I can't do is import their offering donations until it is "Normalized." Which means that I need each row to be 1 record with the fields listed as the column headings. My church has a spreadsheet with several sheets one of which is named Input. I've attached the desensitized file for you to view/play with.
What I need to do is loop through the input sheet and find all the values in range B:4 to GN:387 that are greater than zero or not null (I believe they entered the default as zero so their other calculations would work). Then I need those values to be transferred to the column labeled Offering Amount on the ImporttoAccess sheet. Somehow I also need to define three variables associated with the cell that is contains a value greater than zero.
1. I need the row heading value for the envelope number (Column A of input sheet) which intersects with the amount
2. I need the column heading value (3 field merged) for the date (Row 1 of input sheet) which intersects with the amount
3. I need the column heading value (labeled: GenFund, WLA, or Missions) for the designation of the offering (Row 2 of input sheet) which intersects with the amount
Each of these I need to attach to the value offering amount and insert into their corresponding column fields for that record. I have entered 2 example rows of data into the ImporttoAccess sheet for you to see what I am after. I am sure it's possible since the data is there, But how to go about it.
I have lurked and learned but now this is beyond me-Attached is a sample file - The tab Data is where I start the macro and it is the file( after I have cleaned it up) that I get once a week-I added Column K/L-Blank Count Sheet Format is where the macro puts the info- count sheet is what I want it to look like- Old version is how I used to present the sheets-using subtotals etc-See the print preview to see what old ver and sample look like--
This macro below starts in data and puts Column K / L on the blank and then fills them in. Field 1-5 is all one loc sequence -I have the macro going the first few steps what I have been pulling my hair out attempting to do is --If the part# stays the same keep putting the locations under each other-As I show on Count Sheet and when the part# changes put a page break in and start over again with the new part# and assorted info. The file I get each week to do this with will have from 150 to 200 part #'s. I need a page for each--Each part has from 1 to 10 location's ( The loc consists of Field1-5 alfa-numeric )
I want to put a hyperlink into an existing spreadsheet (which already has some hyperlinks in) but for some reason that option is now greyed out on my tool bar.
I need to insert a drop down box into my spreadsheet, and depending on the option selected, the formula used to calculate the value into another cell changes. How do I implement this in Excel 2007?
To put this in context, I need a list of acids in cell C3. Depending on the acid selected, the formula used to calculate acid concentration in another cell (D3) changes. I need a way to implement this.
I receive a Excel download daily consisting of 6 columns of nine numbers and currently "copy" column 2 and "insert copied cells" into a running spreadsheet. I would like a simple way to have the 9 lines pasted as below rather than as sequential lines.
