Refining the below code which repeats itself for 60 agents per worksheet, for 7 worksheets. Is there a way to loop the code so it counts up 68 cell references until the 'blank cell' condition is met?
[code]'## Agent 1
If Sheets("CSR Dashboards").Range("M3").Value = "" Then sResult = MsgBox( _
Prompt:="CSR Dashboards have been sent.", _
Buttons:=vbOKCancel)
If sResult = vbOK Then
[Code] ........
I've created a userform below and I have a code to write the data entered in the form do the following:
1. Save the data in a worksheet (Asset History) within the same workbook,
2. Copy the newly entered data into a another worksheet (Asset Form).
3. Take an image of the data from worksheet (Asset Form) and
a) Create a HTML image on out look
b) Send out an email (Get the email distribution from worksheet (EmailList)
4. I also want to be able to select the data nad change if any of the information changes using the userform.
Not sure why my code is getting stuck and getting the attached error "1004" when I get to saving and emailing. My guess is that the error is on the Checklist option explicit but I cants fix.
Here is the full coding
Private Sub cmdsave_Click()
Dim cell As Range
Dim rowoffset As Integer
[Code]..............
How/ where to save macros. If I save a macro "normally" ("in this workbook") then it only applies to that one file, right? And I can't use it in any new files.
So what do I do if I want a macro I can use in "all" my Excel files?
And can I email a file containing a macro to a colleague, so he can work on the file, using the macro too?
I ask, because I've had trouble with this in the past (tho in MS Word) where I couldn't get the macro to be emailed along with the file...
Also - how can I get an .xls file with a macro to work in newer versions of Excel? And can I use Excel 2010 to save a file with a macro in an .xls format, so it can be opened in older Excel versions too?
I have a spreadsheet that has accounts number and information in columns A:D
I have Macro Buttons in row one and my column headers in row 2.
What I want to do is email A:C from row 2 to the last row that has information in column A. I tried changing the range of the code to go to the last row but it just goes on to infinity and emails a huge range of blank cells after the data I need.
Code:
Sub EmailMissingAccounts()
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim WB As Workbook
Dim LR As String
Set WB = ThisWorkbook
LR = WB.Worksheets("Missing Accounts").Cells(Rows.Count, "A").End(xlUp).Row
[Code] ......
I am trying to write a script to import, reformat and save sales files for upload into our master server and had a question.
how to make it store a range as a dim to use later. I would use
Code:
Range(ActiveCell,ActiveCell.End(x1 Down)).Select
on all the columns, but there are often blanks in many of the rows and I am worried about potential errors.
There is a column which always has all of the rows populated, so I was hoping to could store the range of that column and use it for all the others, so they all match length.
Sub Testing()
Dim r, LR As Range
Dim k As String
Dim i, Total_Hours, Employee_Row, Last_Row As Integer
i = 0
Employee_Row = 5
For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
i = i + 1
If i 1 Then
If k = "" Then
'First Line
Total_Hours = Range("J" & r.Row).Value
I'm saving LR.Range ("A" & r.Row - 1) into LR as I need to use it outside the loop for the last row here
Sheets("Sheet2").Range("B" & Employee_Row).End(xlUp).FormulaR1C1 = Range("A" & LR.Row).Value
It seems I cannot save a range like thateven using LR as an interger and doing LR = r.Row does not work.
Thanks for all the help so far with this issue. I seem to be getting errors after running a macro a second time. The name of the cell range is changing. I have attached the spreadsheet and I will try to be specific as possible.
Goal 1: I need to input monthly data into the Sales tab and then save it as a CSV file.
Goal 2: Re-open original xls file that has data then Run macro to create reports.
Goal 3: Save Original xls file as a clean sheet to use again next month.
Process:
1 - Insert Data into Sales tab
2 - File/Save As a CSV (Now the CSV file is open)
3 - Close CSV file and re-open the original xls file
4 - Run Macro to create reports
5 - Print Reports
6 - Manually delete current data on all tabs so I have a clean sheet for next month
7 - Save and Close
Problem:
When I open the xls file (next month) to input new data and run the reports I get a Run Time Error '1004" The Pivot Table field Name is Not Valid
I found that the Named Range of cells changed from the original:
I have a range Named "MyRange" which consists of cells "A4:H20". What I am attempting to do is when the user goes to save and or close the workbook it checks that all the cells within the range have been filled with data.
View 3 Replies View RelatedI have previously tried to save the whole original workbook with the following
ActiveWorkbook.SaveAs Filename:="C:UsersPhilDocumentsMy Excel DocumentsRostersIndividual Rosters" & StaffLastName & " " & StaffFirstName & " - Roster commencing " & Format(DateSerial(Range("X11").Value, Range("V11").Value, Range("T11").Value), "dd mmm yyyy") & ".xlsm
but receive a run-time error message:
Run-time error '1004': Microsoft Office Excel cannot access the file
My first request for help is how is the code wrong and how can it be corrected?
Secondly, instead of saving the whole workbook, is it possible to save just a given range of cells as a new workbook, using the format above to give it a personalised filename?
I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.
While all of the above activity is going on, new customers are continuously being in added in G4, G5....
Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010
[URL]
I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.
While all of the above activity is going on, new customers are continuously being in added in G4, G5....
Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010.
I have tried hard to find other solutions with links at the following places but not exactly what I'm desiring:Auto lock cells after data entry when file saved...
Auto lock cells after data entered and SAVED.
Auto Lock Selected Range of Cells After Saving
I should also mention that most solutions either provide for locking/unlocking of all cells but not a selected range. Or they provide for locking/unlocking of a selected range but not for a specific sheet or not after you have saved the workbook.
This is unique in that I'd like it to autolock after i press the save button for a SPECIFIC number of cells. I just wanted to clarify as to not make others think that I haven't used the search function.
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.
Dim wb As Workbook
Set wb = ActiveWorkbook
wb.SendMail "person@address", _
"This is the Subject line"
and this seems to work fine. Could there be any potential problems with this? All senders and recipients should be using outlook. Also is there a way to send the workbook but lose any macros attached. I'm not sure that all recipients will be able to receive workbooks containing macros.
I am attaching workbooks which reflect my original work. I have some questions :
1. How can I allow users to view this with everything working fine? (zipping and emailing was suggested, but I am getting some errors)
2. How can I ensure that the viewing format remains same for everyone viewing it?Al colleague of mine, tried and the formatting is wierd for him
Does any want know how to do the following:
1. In the same Workbook, I have one sheet with email addresses.
2. On Sheet 1, in Cell D3, I have the actual email address I want to send(Used data validation drop-down to get email address)
3. I´ve used many codes and it sends very well, however it does not send the email as a fully functioning workbook.
4. The recipient gets the following message "This book contains links to other sources"
I have a spreadsheet that I email daily after I update the information in it.
I was unable to find a "Code" to do so when I wrote it so what I did instead is use the Sendkeys funtion to emulate the keystrokes as if I were doing it manually. This works well with one exception.
I now have 5 email accounts and I do NOT want it to email via the default. I need to SPECIFY wich account to email thru. But I can ot figure out how to do this.
Sub Email()
' EMAIL Macro
ActiveWorkbook.Save
Application.DisplayAlerts = False
Windows("Load List.xls").Activate
Sheets("email").Visible = True
Sheets("email").Select
Range("a1").Select
SendKeys ("%f")
SendKeys ("d")
SendKeys ("m")
Application.DisplayAlerts = True
ThisWorkbook. Saved = True
When I attach a 1 page spreadsheet to an email and send it to my boss, the spreadsheet is no longer on one page when he opens it. What causes this and how can I fix it on my end or fix it from his end to keep the same format?
View 2 Replies View RelatedAdd worksheet as an attachment to an outlook email by having a button on the spreadsheet itself. However I have added another worksheet which i would like to add to the same email ie 2 attached worksheets to the same email this being decontamination certificate. I have tried to do this myself by copying and pasting some of the code and changing the paths and name but all I get is one or the other or the wrong name to the worksheet.
View 10 Replies View RelatedI am trying write one to search a range of cells and attach all files located in those cells to an email. I can get most of the code to work, but am having difficult getting it to search through specific cells.
View 14 Replies View RelatedI found this link in numerous places on this forum to email just a single worksheet from a workbook using one macro.
HTML Code: ....
I am trying to set my cod up so I can email my excel sheet to multiple people. I tried using a ";" to separate the email addresses (as shown below) but that doesn't seem to work.
With ActiveWorkbook    .SendMail Recipients:="bubba@gmail.com;bubba2@gmail.com", Subject:=s    .Message = m    .Close SaveChanges:=False    End With
I am trying to get a macro to automatically email my workbook out to my distribution list. I have it working but I get a popup telling me:
"A program is trying to automatically send an email on your behalf.
Do you want to allow this?"
Is there anyway I can bypass this message? The code I am using is below:
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = "mbudgell@hotmail.com"
.CC = ""
.BCC = ""
.Subject = "NAME"
.Attachments.Add ActiveWorkbook.FullName
.HTMLBody = MyHTML & "Hi,
I have an icon in excel that i would like to attach a macro to and send sheet 2 to a specific email address.
View 4 Replies View RelatedI need to know how to send an email from Outlook using a rather large email address list (500 addresses) that resides in Excel.
Also, I need to know how to hide the individual recipients and send it out under a title name. I want all of the recipients to see the title name only - and not each other.
I am building a weekly email to my clients giving them some data on their account. When i send through file-> Share as html email, the formatting gets all screwy.
View 2 Replies View RelatedI put together some code in a frankenstein fashion to have a report sent to different people based on the sheet name.
I am trying to automate this so that the macro looks at each sheet name and then converts the sheet to a PDF, attaching and sending an email to the relevant person.
However I have run into a referencing issue whereby it does not pick up the data from each sheet. I'm guessing this is to do with the way I have used ActiveSheet or sh but I have not figured it and really need a fresh pair of eyes on it.
It should be looking at each sheet in turn but instead it is taking the data from the activesheet and duplicating this based on the number of worksheets in the workbook.
I have a workbook with several sheets- lets call it MyWorkbook (The VBA Project should be locked so no one can view code). What I need is to run a code from MyWorkbook to make a copy of this – call it NewWorkbook. Now in NewWorkbook I need to hide sheets Kong,Bong and Dong……then save it as ‘Workbook name’& Date & time. Then email NewWorkbook. (I don’t want to keep any copy of NewWorkbook)
After that I need to Save MyWorkbook (so basically MyWorkbook keeps getting updated with new info everyday, and there are no hidden sheets)
VBA Codes should be password protected in both workbooks. I tried to write the email code , this is what I have so far.
I've got a macro that will email a spreadsheet out to any of a dozen people. Each of these people will need to use this macro to send a copy of their spreadsheet to me and my boss, but not necessarily to everyone on their list. I know how to send it to me, and to my boss, but how does Excel know to send it also to the person who's sending it (I know they can go to their outbox to retrieve it, but for neatness sake I'd like them to get a copy in thier inbox). I'm using Outlook 2003 and Excel 2003.
View 14 Replies View RelatedThis is a followup to a post I had marked as solved, and it's really not. Below are two different Email routines I've tried, both based on macros by Ron DeBruin, neither of which fully satisfy my need.
One routine creates a copy of a spreadsheet and mails it. The problem with that is that my source spreadsheet has cells containing 255 characters or more, and Excel truncates the cells after 255 characters, so all of the data isn't going into the new spreadsheet.
The other creates a new spreadsheet, copies the data from my old spreadsheet, and pastes it into the new spreadsheet. Problem with that is the new spreadsheet doesn't have my footer or headers and isn't formatted to print correctly.
I need to either find a way to get past the 255 problem, or find a way to copy a properly set up spreadsheet and paste my data into it. My code is below: This one copies the spreadsheet and mails it
I'm using the With ActiveSheet.MailEnvelope command in vb to send an email and it works fine, but the range I am selecting includes macro buttons on the sheet.
It is possible to prevent macro buttons from been printed by selecting Format Control > Properties on the button and unticking Print Object (off by default anyway).
Is there any equivalent way of preventing macro buttons from appearing in emails when a range is emailed rather than printed.