Emailing Worksheets In PDF Based On Sheet Name
Oct 16, 2008
I 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.
View 8 Replies
ADVERTISEMENT
Feb 24, 2007
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 Related
Oct 13, 2009
I have an original workbook that I use daily stored in a folder called 'Live'. I also have this same workbook saved in a different folder call 'In Progress' because I am constantly improving and making changes to it. While I'm in the revision mode, I keep it in this folder. Once I've completed my revisions, I move it to the folder where the live workbook is located and replace the old version. Therefore, I have two copies of the workbook at all times. The name of the workbook in both folders is the same (IRQT)
I have a macro in this workbook that will allow you to save a copy of the workbook in a folder called IRQTArchive. The macro will create the folder if it doesn't exist, then store a copy of the workbook using a name in a cell. After that is complete it renames the workbook back to the original name (IRQT).
This macro works perfect in my live version, but when I execute the macro in the 'In Progress' version, I get a Compile error: Sub or Function not Defined when it reads the dirExists command in the first line of code. The code in both sheets is identical. Here are the first few lines of code. The command dirExists is highlited in yellow when it errors out.
View 11 Replies
View Related
Nov 14, 2009
I am using a macro to e-mail any work sheet with an address in A1. I would also like to attach a file that will be listed in cell E1 of that worksheet. The file is different for each work sheet that is being sent but will be listed in the same cell of each work sheet.
This is the code I am using (i got it from ron de bruin) when I use the .Attachments.Add (C:est1.txt) it works but i can not seem to figure out how to get it to read the file address in the E1 cell. The code i am trying to use is .Attachments.Add = ws.Range("E1").value.
View 3 Replies
View Related
Nov 21, 2011
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.
PS.Using Excel/Outlook 2010
View 1 Replies
View Related
Jan 13, 2009
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.
View 2 Replies
View Related
Jul 7, 2007
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.
View 9 Replies
View Related
Jul 17, 2006
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
View 3 Replies
View Related
Jan 31, 2007
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"
View 9 Replies
View Related
May 16, 2007
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
View 4 Replies
View Related
Aug 20, 2014
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 Related
Nov 29, 2012
Add 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 Related
Aug 22, 2007
I 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 Related
Mar 30, 2009
I found this link in numerous places on this forum to email just a single worksheet from a workbook using one macro.
HTML Code: ....
View 12 Replies
View Related
Feb 2, 2012
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
View 6 Replies
View Related
Feb 7, 2013
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] ........
View 9 Replies
View Related
Sep 26, 2007
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,
View 9 Replies
View Related
Jan 12, 2005
I 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.
View 3 Replies
View Related
Jun 25, 2013
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 Related
Jun 25, 2014
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]..............
View 14 Replies
View Related
Nov 13, 2009
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.
View 2 Replies
View Related
Dec 18, 2009
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 Related
Jan 6, 2010
This 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
View 2 Replies
View Related
Nov 23, 2012
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.
View 2 Replies
View Related
Feb 7, 2014
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] ......
View 2 Replies
View Related
Sep 8, 2004
I'm trying to setup a template for people at work to use, where they would fill in fields i setup (Account number, customer name etc). They would then hit a macro that would email the spreadsheet to a certain group of people.
My problem is i don't want them to be able to use the macro unless all the fields have been filled in with a value. I would want all fields to at least equal something, either a number or a word, is there a way to do this?
View 9 Replies
View Related
May 15, 2007
I understand how to email from Excel, but what I need to know is how to automatically delete the emails I just sent from my sent items. I use the code in Excel to email out many reports with attachments every morning, and need to delete them right after I send them to conserve space. Can this be done form within Excel code?
View 9 Replies
View Related
Oct 17, 2011
Here is my code below for a program that opens a user interface, lists all the names of tabs in the spreadsheet in a list box. By selecting an item (or sheet name) in the list box then clicking "email" a specific area of that selected sheet is sent to an email address located on the sheet.
What I would like to have happen is with the click of a button email each sheet to the specified email address. So if there were 100 sheets rather than clicking on each name then clicking email, have one button to email the each individual sheet to the associated email address. I am guessing something like a "For" loop would be required but am not quite sure how to implement.
Code:
Private Sub CommandButton2_Click()
ActiveWorkbook.Close
End Sub
Private Sub ListBox1_Click()
'this will set the label caption depending on which sheet is selected in the list box
Label1.Caption = Sheets(ListBox1.Value).Range("b2").Value
[Code] ..........
View 8 Replies
View Related
Feb 15, 2012
I'am trying to make a button that will let me send a email via outlook. trick is work book is password protected but need to send some of the data contained to perssonel that dont have the password so sending teh complete work book doesnt work just need something simple. I have learned all ( little) i know from reading and searching here.
no links to other sites as my firewall here at work will possibly reject it
View 9 Replies
View Related
Aug 26, 2013
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?
View 3 Replies
View Related
