ActiveWorkbook.SendMail
Apr 8, 2009
I have the below code,
But i want to adapt the "ActiveWorkbook.Sendmail" so I can attach my current workbook as it is, to the email.
Private Sub CommandButton10_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "you@email.com"
.CC = "me@email.com"
.BCC = ""
.Subject = "Testing send to multiple emails."
.Body = "Please review." & vbCrLf & vbCrLf & ActiveWorkbook.FullName
.Send 'or use .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Dim Msg, Style, Title
Msg = "E-mail has been sent " & Chr(13) & Chr(10) & "Press OK to continue."
Style = vbOKOnly + vbInformation
Title = "Open Issues List"
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
End Sub
View 9 Replies
ADVERTISEMENT
Mar 23, 2007
Is there a way to use the function Sendmail without sending the Workbook, sheets or any attachments at all. I just would like to push a button in my Workbook so that a mail goes to the recipient saying for example: "Check our shared folder.
View 4 Replies
View Related
Sep 15, 2007
I am using a sendmail macro I found online. I cannot remember where I found it or who wrote it.
I need to change it so that it references a cell (HTOM!C2) for the email instead of a static address. The portion I need to change is 14 lines up from the bottom.
Option Explicit ....
View 7 Replies
View Related
Mar 19, 2012
I have the below code of which I would like to addresses added to the CC list Also, Would it possible to add a range as the body? E.G:
.CCaddress = "somebody@mail.com"
.CCaddress ="somebody.else@mail.com"
Body text = Range("A1"& "" &"B1")
Code that need the above inputted:
With ActiveWorkbook
.SendMail Recipients:=Array("steve.howard@kuehne-nagel.com"), _
Subject:="610 Cambridge " & Format(Date, "yyyymmdd") & " " & "Counts"
[add cc address + body]
.Close SaveChanges:=False
End With
View 5 Replies
View Related
Jul 3, 2009
I noticed all the code that i have on a userform using activeworkbook will relate to whatever the last active workbook was "who would have guessed hay" Seeing thisworkbook used a lot i decided to go through the whole project and change all activeworkbook too thisworkbook.sheet1 During this i also made a few other changes that i was confident in, When i run it now it errors on the thisworkbook.sheet1.
Is this the wrong way of using thisworkbook to make sure the code always deals with the correct file. (will this ever be affected if the file goes through a Save As)
Or is it during my over conifedents that i have made a error somewhere else and the debuger is point me here cos it hates me.
View 2 Replies
View Related
Jan 2, 2010
I have a userform that presents the user with 4 choices and once they click on one choice a macro runs that filters and formats the data and when the user clicks on the button start over it will reopen the file they were just using prior to the changes occurring. The reason for this is the same spreadsheet is printed up to 4 times with different information (remember the part where the data was filtered?). So basically the user clicks on option 1 the macro runs formating the page and then the user clicks start over (different userform then the first one) and the spreadsheet closes and reopens before the changes.
The way I have excel reopening the file now is by having the user input the file name into a text box and clicking OK this passes the file name to the open code in VBA. How can I tell the code the name of the sheet after the sheet is no longer active (because a userform was made active)? Basically the end goal is for the user to click over and excel will just reopen the file without any user input.
View 3 Replies
View Related
Feb 16, 2009
how I can make the following work no matter which worksheet I might be on?
ActiveWorkbook.Worksheets("Sheet16").sort.SortFields.Clear
View 9 Replies
View Related
Nov 17, 2009
I run the macro on a currently open workbook (name could be anything) and open another workbook (name known) and step through the sheets copying various blocks of data across to the first workbook. I need to Activate the 2 workbooks alternatively as I move data back and forth and move from sheet to sheet. What I can't work out is how to determine in code what the "unknown" workbook name is so that I can make sure I activate the right workbook in the code.
I tried Activating the workbook with the "unknown" name and setting a variable = ActiveWorkbook.Name. However the value of this variable changes during the code run when you activate the other book. Is there any way to collect this value and "freeze" it so it won't change as the code runs? At the moment I have got round it by ensuring the "unknown" book is the only one open before I start and refer to it as Workbooks(1) in the code.
View 2 Replies
View Related
Jan 21, 2009
I have the following code that is going to trigger an export macro and an import macro allowing the user to upgrade the version of their report;
Sub Upgrade_SLA_Report()
x = MsgBox("Is the file you wish to export from open?" & vbCrLf & vbCrLf & "If it is click yes" & vbCrLf & "If its not click No" _
& vbCrLf & "If you need to abort the macro click cancel", vbYesNoCancel)
Select Case x
Case 6
MsgBox "Please use the Windows menu in Excel to ensure that the file" _
& "you wish to export from is the ActiveWorkbook!", vbInformation
' Missing step
If ActiveWorkbook.Name = ThisWorkbook.Name Then Exit Sub
ExpWb = ActiveWorkbook.Name
Case 7
On Error GoTo Handler
.......................End Select
Problem is, I have a missing step within Case 6 which allows the user to ensure that the file they are exporting from is the ActiveWorkbook.
View 9 Replies
View Related
Sep 25, 2009
I am trying to build a macro to be placed in a blank spreadsheet for use as a template.
My development platform is Excel 2003 on Windows XP SP2. The script was initially recorded as a macro against a single data file with absolute range references and worked both on my develpment platform and on the production platform (Excel 2000 on Windows 2000). However, I have been having fits trying to convert it to dynamic addressing for the PivotCaches.Add. I develop Excel solutions a few times a year and this pivot table is the deepest I have dug into OLE code, so I am not an expert and could have a simple problem. I included the entire macro because I am unsure if there are sideeffects to what some of it is doing. I am getting a 1004 error "The PivotTable field is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field."
Since I do not get this error with the absolute references, I am puzzled as to what to fix. That is another reason for supplying the entire macro: ...
View 9 Replies
View Related
Jun 23, 2006
I have a workbook that is used by many people on a network- I am editing and saving data throughout the day, and the other users are constantly having to close and re-open thier file in order to have the most recent data. today i came across the '.UpdateFromFile' command in the help file, which would be perfect, if i could only get it to work. im guessing the problem is the expression, which would be the active workbook, but' ActiveWorkbook.updatefromfile' is not working. The file is on the network, remember, im guessing this is the problem. So, im also guessing i just need to get the syntax right for the filename.
View 4 Replies
View Related
Jul 3, 2006
How can I tell if a Sheet is protected (via VBA) ? (Excel 2003)
Something like: "If IsProtected. ActiveWorkbook.Sheets(1)..."
Returnig True or False will be fine.
View 3 Replies
View Related
Jun 29, 2007
I have used a code to find the character "-" and replace it with nothing ("") from several named worksheets across my workbook, which OzGrid helped me with before, and it worked fine. Now I've added the code into my module (at the bottom) it doesn't work. The rest of the code works fine, and it doesn't come up with any errors when I run it, but it just seems to ignore the 'find and replace' part of it.
Sub split1()
Dim fromSheet As Object
Set fromSheet = Worksheets(1)
Dim fromRow As Integer
Dim toRow As Integer
Dim lastRow As Integer
Dim errCount As Integer
Dim thisSectionName As String
Dim i As Integer ' loop counter
Dim rng As Range 'found range
Dim emptyCellAbove As Range
Dim rng1 As Range
Dim arng1 As String 'Address of 1st hit rng1
Dim ws(7) As String
Dim ii As Integer '2ND LOOP COUNTER
Dim iii As Integer '3rd Loop Counter
View 9 Replies
View Related
Jul 4, 2007
This piece of code successfully replaces a module in a range of workbooks -
Sub CopyOneModule01()
Const strModName As String = "Module1"
Dim FName As String
Dim vWbkNames As Variant
Dim lLoop As Long
vWbkNames = Array("CFADS01", "CFADS02", "CFADS03", "CFADS04")
With Workbooks("CF266") 'copies from here
FName = .Path & "code.txt"
.VBProject.VBComponents(strModName).Export FName
End With...........................
View 2 Replies
View Related
Apr 15, 2009
i'm tring to count how many shape in the activeworkbook are ovals like so :
View 3 Replies
View Related
Oct 15, 2009
i am creating an add in that will allow me to display all pivot tables in a workbook, their name, sheet location and source data in al listbox (called LBPivot) on a form (called FrmPivot)
My problem is that i am not sure how to make sure it points the focus to the activeworkbook and not the actual add in workbook?
View 6 Replies
View Related
Mar 21, 2008
I recorded the following macro to select all the worksheets in the Activeworkbook by clicking on the first worksheet and then hitting Shift Tab and selecting the final worksheet (thus '[Group] selecting' all worksheets in the active workbook).
Sub Macro1()
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet1").Activate
Range("A1:B2").Select
End Sub
How do you generalise the Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select if the number and names of worksheets varies from workbook to workbook?
View 9 Replies
View Related
May 17, 2009
I'm developing an excel addin using Visual Studio 2008. At the moment i'm trying to display a popup showing the value contained in cell A1. However, i'm getting a runtime error saying that xlWorkBook is null. I've been able to get this to work if i open a workbook but i want to be able to execute my code on the currently active workbook. I thought that ActiveWorkbook (also tried ThisWorkbook) would let me do this but i'm having no success.
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
...
...
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
xlApp = new Excel.ApplicationClass();
xlWorkBook = (Excel.Workbook) xlApp.ActiveWorkbook;
xlWorkSheet = (Excel.Worksheet) xlWorkBook.Worksheets.get_Item(1);
MessageBox.Show(xlWorkSheet.get_Range("A1", "A1").Value2.ToString());
View 9 Replies
View Related
Oct 13, 2009
I have a macro that creates and saves an XML file with a specific file name. Is there a way to modify this code in such a way that the user will be able to specify the path?
Set wb = Workbooks.Add
NewFileName = "Export For " _
& "Cust " & Customer_Data.Range("custName") _
& ", LOB " & RequestLOB _
& ", Type " & RequestType _
& ", Site " & RequestSite
On Error GoTo DoNotReplaceExistingFile
ActiveWorkbook.SaveAs Filename:=NewFileName & ".xml", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
View 9 Replies
View Related
Jan 30, 2007
I do not want ThisWorkBook to close prematurely whenever the specified Activeworkbook is closed without saving because there are other tasks to complete. The Workbook_BeforeClose dun seem to fix it for my case below.
Here’s my situation. I want to copy data from a range of a particular protected sheet in 5 (password-protected) workbooks of a similar format, to 5 worksheets(unprotected) in ThisWorkBook, which is the Master Workbook. The password of the protected sheet is common to the 5 workbooks. The passwords of the 5 workbooks are contained in a sheet in ThisWorkbook. After copying the data from Workbook1 ( 1 of 5), ThisWorkbook are prompted for closure, when Workbook1 is closed without saving. Responding to the prompt to save or not to save, forces ThisWorkbook or MasterWorkbook to break the For-Next loop. By setting Cancel = True in Workbook_BeforeClose, I can stop Thisworkbook from closure, but I will still break the For-Next loop. I have considered the method of copying data from a closed workbook, but I dun think VBA allows copying or referencing a closed workbook which is password protected at workbook and worksheet level.
Here’s my coding :
Sub UpdateMaster()
Dim MasterWB As Workbook
Dim TempWB As Workbook
Dim Source As String
Set MyWB = ThisWorkbook
For i = 1 to 5
Set TempWB = Workbooks.Open (Filename:=WB(i), Password:=password(i) )
TempWB.Sheets("ProtectedSheet").Unprotect Password:="SheetPasswd"
MyWB.Sheets(i).Range(XXX).Value = TempWB.Sheets("ProtectedSheet").Range(YYY)
TempWB.Close False ‘dun want to respond toclose ThisWorkbook else it breaks for-next loop
Next i
Application.Quit.
End Sub
View 9 Replies
View Related
Aug 2, 2006
I have a csv .txt file that I am importing into Excel, and then creating a pivot-table from that data. This results in 2 tabs within my workbook, pivot-table and data. Using the following code does everything I want it to, but when I re-open the saved .xls file I only have the pivot-table sheet. I'm specifying to save the full workbook, aren't I?!?
Sub format_bgas_flowtracer()
Dim v_excel As Excel.Application
Dim v_worksheet As Worksheet
Set v_excel = CreateObject("Excel.Application")
On Error Resume Next
Kill "c:oh_for_gods_sake_JUST_WORK_YOU_STUPID_SODDING_THING.xls"
On Error Goto 0
v_excel.Workbooks.OpenText FileName:="D:gas_flowtracker_out.txt", Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _ ...............
View 4 Replies
View Related
