I've seen a few posts recently asking about sending either sending a range or a worksheet in an Outlook email as the body of the message through code. I've been looking at this and think I've come up with something that might work. I'd appreciate it if any of you XL kings and queens would take a look and see if the code works OK on your machine. I've sent a few messages to myself (sad I know ) and they seem to work well.
Here's the code. You need to set a reference to the Outlook object Library AND the Microsoft Scripting Runtime in order for this code to work.
Option Explicit
Sub SendRange()
'Sends a specified range in an Outlook message and retains Excel formatting
'Code written by Daniel Klann 2002
'References needed :
'Microsoft Outlook Object Library
'Microsoft Scripting Runtime...........
Sub SendEmail() 'References needed : 'Microsoft Outlook Object Library 'Microsoft Scripting Runtime Dim olApp As Outlook.Application, olMail As Outlook.MailItem Dim FSObj As Scripting.FileSystemObject, TStream As Scripting.TextStream Dim rngeSend As Range, strHTMLBody As String Dim Range1 As Range, Range2 As Range alertsSheet = ActiveWorkbook.Name Workbooks(alertsSheet).Activate Sheets("STATS").Select
Issue with this code is it is selecting wrong source range while generating email.
MsgBox Range1.Address is giving results as "$A$1:$B$1,$BX$1:$CA$1" and MsgBox Range2.Address is giving results as "$A$32:$B$38,$BX$32:$CA$38" but MsgBox rngeSend.Address is giving results as "$A$1:$B$38"
And the email generated as output is having only the data from range "$A$1:$B$38". Is there anything wrong with the way I am specifying the source range ??
I need that the code should replace strHTMLBody with the data in Range1 and Range2. I checked the tempsht.htm file and found that this file also have the same wrong data i.e. data from range "$A$1:$B$38"
Needless to specify that I am working with data having few hidden columns and need to consider only visible data.
I am trying to write a macro to compose an email in outlook without sending it. Ideally the macro would compose the email and the email would be left open for me to inspect before sending. Below are the details.
Column A contains the addresses Column B contains the CC addresses Cell C2 contains the message
I can change where the above info is located/arranged if need be.
My spreadsheet automatically generates the email addresses, which are dynamic. For control purposes my employer does not want me to have the email automatically sent, being the reason for me to want to inspect the email before sending it.
i have the following code in my Excel worksheet. This code successfully send out email to our customers when if the criteria is true. I have tested this at home and it works perfectly as I use Outlook at home. However when I take this code to work I couldn't get this working as at work we use Outlook 365 and we use web Outlook, OWA.
Is there a setting I can specify to use OWA as I don't think I am allowed to install outlook at work.
I need of some way to capture a screen shot of a program on screen and send it to a specified email address at a certain time of the day. I would not think its impossible, could it be done using vba code?
I have an excel spreadsheet of 500 users which contains these details:
A1 = Email Address B1 = Mobile Number C1 = User Name D1 = Department E1 = Manager F1 = Handset
What i am after is something which will let me be able to use outlook to email each user their own details. So the "TO" filed in outlook to be A1 Email Address Subject to be - Company Mobile Phones Then in the body of the email to have the users details, so something like.
Mobile Number - 0123456789 User Name - Stewart Layzell Department - IT Manager - The Boss Handset - Iphone
I have an excel sheet on which to address, cc address, subject , attachement location and file name is mapped. Can some one help me out but executing the macro it will pick the file from the location and send to the Recipient with cc and subject and attach the file accordingly. The excel sheet is attached for your reference.
I need to send 25 mails on daily basis to my client. All fields in mail are common except recipient and attachments. Every mail has 3 excel attchments. So i created a macro in excel which sends mail as per my requirement but picks up only one attachment from the path i have mentioned in the vb code. Whole macro is running perfectly. I just want to know what modification i should do in that code to attach more than one excel file to the mail from the path i have given. Below is the code:
Sub macro1() Dim sFolder As String Dim Folder As Object Dim file As Object Dim fso As Object
I find out a code to create a PDF (with opening the Save As dialog box) from an active worksheet, but I can't find out how to send this PDF by e-mail (Outlook). The code is working till the words 'Set OutApp'.
Just what I want is to send the active worksheet as PDF (as attachment) by email (Outlook). Here the present code.
Code: Sub SendPDF() ' ' SendPDF Macro ' Dim OutApp As Object Dim OutMail As Object Dim v As Variant v = Application.GetSaveAsFilename(Range("E2").Value, "PDF Files (*.pdf), *.pdf")
I have created a spreadsheet that displays new dates in column D based on provided dates in Column B and recurrence intervals selected in column C. The dates in column D are when a training deadline occurs. I have been trying to get a macros written so that when a cell in column d is updated, an outlook appointment message is sent to the person doing the updating. The idea here is that anyone can use this without future coding (so their address would be placed in cell say A1)
So far all i have gotten is excel to send an email to me with the excel spreadsheet as an attachment, not really what I want. Again, I dont want somethign specific to my computer settings, I want to be able to send the spreadsheet out with simple instructions for the user to change their email in cell A1 and then every time a cell changes in column D, it automatically generate an appointment reminder for the new date.
I currently have a tab on my spreadsheet named CSV. I have a macro that when called saves the information on that tab and sends it to a folder saved as a CSV file.
However this tab has a lot of information on and i want it so send it with only the information needed at the time. the data i want to send is in Columns A to AM and has rows down to 7392. I only want it to save the file with the data in the rows that has "Export" in Column AQ ignoring any other rows of data that i dont need it to send
I dont want anything to happen to the CSV tab ie delete lines etc as files will be getting sent regulary from that sheet still but with the data constantly updating in different cells
I am trying to send bulk emails from my excel 2010 - however I am getting a POP UP. find the screen shot in the enclosed word document So every time a new mail is sent from excel we need to press the button allow Is there a way where I can turn off this warning.
I am looking to set up a vba code for sending an email to a recipient based on whether the row AI19:AI30 shows the value 3,6,9 and i want it to send an email showing all of the 3 data all of the 6 data and all of the 9 data.
I have attached my code so far.
VB: Private Sub Workbook_Open() Dim w As Worksheet, c As Comment For Each w In ThisWorkbook.Worksheets If UCase(w.Name) <> "All Trades" Then Select Case w.Range("AI19:AI30").Value Case Is = 3, 6, 9
Normally i am able to send mail using gmail with example text by typing in body field. But I want to send Range(A8:D17) as message body.
First I defined body1 as variable range, then i set body1 as value of my required range in body of message i used variable 'body1' but it is giving error "run-time error '13' , Type mismached "
[Code] .....
Where I need to change in ordger to send mail using range as body .
I am using this code to hide or unhide rows of text on another sheet:
VB: Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)
Dim sAddress As String Dim sValue As String
'Get the address of the cell that changed without '$' signs sAddress = Target.Address(False, False)
[Code]....
When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.
The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..
Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)
Code solution can be entered directly beneath:
VB: If Target.Value = "Not Pursuing" Then ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True
I am using a code copied from Ozgrid to email a worksheet but the code doesn't seem to work?
Sub Mail_Sheet_Outlook_Body() Dim rng As Range Dim OutApp As Object Dim OutMail As Object With Application .EnableEvents = False . ScreenUpdating = False End With
Set rng = Nothing Set rng = ActiveWorkbook.ActiveSheet.PrintRange 'UsedRange Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon .......................
I have a range in an Excel worksheet named 'Request'. I want to paste this range into the body of an email and send to sepcified recipient via outlook. I tried the following code, but this does not seem to work. Am I not referencing to Outlook correctly?
Private Sub cmd_Send_Request_Click() Dim objol As New Outlook.Application Dim objmail As MailItem Dim varBody As String Dim objdata As DataObject Set objol = New Outlook.Application Set objmail = objol.CreateItem(olMailItem) Set objdata = New DataObject Application.Goto Reference:="Request" Selection.Copy objdata.GetFromClipboard varBody = objdata.GetText With objmail .To = "kully.rai@ca.com" .Subject = "Benchmarking Request" .body = varBody & vbCrLf & vbCrLf .NoAging = True .Display End With Set objmail = Nothing Set objol = Nothing End Sub
My range("C4") has the time "10:00 AM". The field has been formated to DATE format. With my code to create an Outlook appointment, I can get all of my required fields to populate from the spreadsheet except for the time. I would like to populate the time for the appointment with the data in C4.
I am attempting to create a user friendly spreadsheet and i am getting no where. i want to have someone populating a spreadsheet with data with a conclusion and final. at the end of the range if the outcome is below a set percentage they will click the hyperlink which will open outlook to a preset distribution list depending on the percentage. i have all this done but what i need is that when the email opens it copies the range of cells into the email so that all they have to do is click send. this seems simple enough to say but it appears that Ms has decided this is not possible.
[C1] My dynamic email subject [C5:C10] My dynamic email body message My recipient is always same "burak@burak.com"
Now, I need a VBA code, that will automatically open a blank new outlook email window fill the fixed recepient fill the dynamic subject and body of the message from a range in my workbook.
Then just stop there so that I can attach some different files each time and send myself.
I have had some help with this (thanks!) but am stuck.
Can someone add to the code below and make it so that multiple tasks, with multiple assignees, can be created from a range of cells, such as:
Column A Column B Row 1Task SubjectAssign to Row 2GV LP for AZJohn Brown Row 3GV LP for COJohn Brown Row 4GV LP for ILSuzy Smith Row 5GV LP for INSuzy Smith Row 6GV LP for KYSuzy Smith Row 7GV LP for MNSuzy Smith Row 8GV LP for MSBob Barker Row 9GV LP for NCBob Barker Row 10GV LP for NJBob Barker Row 11GV LP for NMBob Barker Row 12GV LP for NVBob Barker Row 13GV LP for NYBob Barker Row 14GV LP for OHBob Barker Row 15GV LP for SCBob Barker Row 16GV LP for WVBob Barker
(Sorry, it didn't translate well. The "GV LP for AZ" is cell A2, "John Brown" is cell B2.)
I am writing a code wherein I want to assign specific excel range to a declared variable and then paste this excel in outlook message ody but I am finding difficulty in assigning that range to variable " brng"
I think code is right and issue is there in excel setting.
Code: Sub mailer() ' ' ' Dim Ash As Worksheet Dim brng As Range Dim OutApp As Object Dim OutMail As Object 'Windows("Copy of FF RPL REPORT_JULY").Activate
Am working in Excel 07, but this would need to work in 2000 as well.
Need a macro that will...
1. Select a range of cells from B4 to RX. X is defined as the last row where Column A has a value. 2. Copy the visible cells 3. Open an email in Outlook (not via the email workbook function of excel), enter "Submission" into the title, enter "Dear X," insert 2 returns. 4. Paste the copied table (not the workbook, just what is on the clipboard) into the body the email.
I have a macro which I manually have to change the range in order to run the macro, e.g. Set rngData = Range("B4:I12")
rather than having to change the range for each macro, I was wondering if I could run the macro for the highlighted area. I have tried this, but doesn't seem to work.
