Select Worksheets Dialog Box - How To Change Code From Print To Send To Email

Sep 29, 2012

I am making custom time sheets to suit our agriculture business - these excel sheets get sent out to the different farm managers who send back in staff times.

This code below is brilliant and works perfect for our needs. However I need to be able to emailPDF the sheets not print.How / where do I change code so the selected sheets go to Save & Send via email as a PDF instead of going straight to the default printer .( hard copy )

VB:
Option Explicit
Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet

[Code] .....

View 5 Replies


ADVERTISEMENT

VBA - Code- Send Worksheets Named In Col B To Email Addresses In Col A

Jan 4, 2010

I have a code that i got from here which will send the Workbooks named in col A to email addresses in col B, as below..

but i need it to send the named Worksheets from the currently active Workbook.

The name of the Worksheet will be contained in col A.

Sub SendWkbs()
Application.ScreenUpdating = False
Dim wks As Worksheet
Dim iRowA As Integer, iRowB As Integer
Set wks = ActiveSheet
iRowA = 2
On Error GoTo EH
Do Until IsEmpty(wks.Cells(iRowA, 1))
iRowB = 2

View 9 Replies View Related

VBA Code To Send Email To People Whose Email Address Is In Access Table

Apr 29, 2014

I have specified the email addresses in the code to whom the email will be sent when the user presses "Send Email" button but now I want to add all the addresses in the Access table and write down the code that will send an email to those people whose addresses are in the Access table.

[Code] .....

View 5 Replies View Related

Send Email On Cell Change Works

Aug 7, 2009

I am sending an email when the value of a cell changes, [url]

I am using the following code in my worksheet....

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("AE7"), rng) Is Nothing Then
If Range("AE7").Value < 20 Then EmailOut 'MyMacroName
End If
End If

What I would like to do is increase the range that this applies to.

Range from AE7 to Range Y3:AE250.

I would like to Include the Range A3:A250 in the subject/body.

So If AE7 has changed, A7 (along with some text "This cell has changed, do X Y Z") would be somewhere within the email.

View 9 Replies View Related

VBA Code To Send / Email From Excel

Feb 3, 2012

I have a macro that is currently set up to save a draft of my email from Excel. Feedback I've received from my peers is that they would like this macro to Open the drafted email it creates instead of the user having to navigate to the draft folder to open it up.

Is there a part of the code below I can modify so this happens?

//Sub Mail_workbook_Outlook_1()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

[Code]....

View 1 Replies View Related

Vba Code Used To Send Lotus Notes Email - A Few Edits

Feb 27, 2008

1. I dont want this to auto send. I want it to compose the mail and leave it ready to hit the send button in lotus notes. How do I do that?

2. Would like to include multiple emails. If I put two emails (separated by comma or semicolon into cell C19, Lotus notes sends as one address and it bounces. (This is not terribly important, but would be a nice feature)

3. Would like to insert the signature line that's already configured on lotus notes. Since this auto sends the email, the signature is not added. Any idea how to do this?

4. Now this would be really nice, tell the macro which database to use. The one I want to use is not "my" email, but a shared email. It lives on server "Notes1/recovery" , in folder "mail" with a database named "company.nsf". Is there a way to do this?

Here is my code that works, but needs the above features.


Sub SendNotesMail()
Dim Maildb As Object
Dim UserName As String
Dim MailDbName As String
Dim MailDoc As Object
Dim Session As Object
Dim Recipient As String
Dim Subject1 As String
Dim ccRecipient As String

View 9 Replies View Related

How To Send Excel Invoice To Email Address Using Macro Code

Jan 22, 2013

I have a template invoice in excel. What I want is a macro code that when it is run the open template invoice should be sent to a specific email address !!

View 5 Replies View Related

Enable Macro Automatically - Then Send Send Email

Sep 7, 2008

I used Scheduled Task to set up my spreadsheet to open daily. I have the code with assistance to pull out the due date items and place them into an email.

I have come across XLSTART/AUTOEXEC/ACTIVATE...ETC...
But cannot figure out the code that will automatically "enabling macro" once Scheduled Task opens the spreadsheet?

Then once the macro runs, the email with the due dates, how can this auto send without user interaction?
(currently I would have to hit send)

I am trying to make the process totally automated to open the spreadsheet at a certain time, send the email with due dates and close the spreadsheet.

Following code in ThisWorkbook--

Private Sub Workbook_Open()
Check_Date_Send_Mail
End Sub
Code in Module1--

Option Explicit
Sub Check_Date_Send_Mail()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnDate As Range, rnValue As Range
Dim stAddress As String, stMsg As String
Dim stRecipient As String, stSubject As String
Dim stPost As String
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Sheet1")
With wsSheet
Set rnDate = .Range("d2:t23")
End With

View 9 Replies View Related

VBA Outlook Email Auto-send Routine - Check If There Any Attachments In Email To Be Sent

Jul 23, 2014

I have a macro to send emails to a group people that based on various criteria, it will attach a number of files to a recipient's particular email. This is a small portion of the code that does the attachment adding:

Code:
If Range("B" & a) = "Y" Then
If citChev "" Then .Attachments.Add citChev
End If
If Range("C" & a) = "Y" Then
If citMits "" Then .Attachments.Add citMits
End If
If Range("D" & a) = "Y" Then
If citToyo "" Then .Attachments.Add citToyo
End If
If Range("E" & a) = "Y" Then

[code].....

Most people get several of the files attached to their email and all works fine. But, there are certain conditions when all the IF() stmts fail where a recipient will not get any files attached. I do not want to send the email if this is the case, but it currently is sending it.

Is there a way after all the IF() stmts have processed to check to see if this current email has any attachments assigned to it? (IF .Attachments "" Then...) does not work.

View 2 Replies View Related

Send An Automated Email To Typed In Email Address

May 20, 2008

I am trying to send an automated email by use of a "email" button. What I want it to do is to pop up a input box that will ask me who I want to send the email to, and once I hit ok it will send open up outlook and send the email. I have the code to work if I want it to be sent to a specific email address, but I can't seem to get the email address entry part to work. I will attach my code as it lays right now.

Private Sub CommandButton2_Click()'Need to reference: Microsoft Forms 2.0 Object LibrarySet OutApp = CreateObject("Outlook.Application")OutApp.Session.LogonSet OutMail = OutApp.CreateItem(0)strbody = "This is the most up to date copy of EAS Tracking 2.0 as well as the Resource Planning Sheet."attachmnt2 = "C:My DocumentsResource Planning Sheet_External.xls"On Error Resume Next'?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|? BELOW IS WHERE I CAN'T GET TO WORK!!!With OutMailDim range As Longrange = Application.InputBox("How many copies do you want?", "Number of Copies").To = range.Subject = "This Weeks Reports".Body = strbody.Attachments.Add (attachmnt2).Display.SaveEnd With'__________________________________________________attachmnt3 = "C:My DocumentsReport DataWork Request Tracking Data FolderEAS Request 2.0.xls"On Error Resume NextWith OutMail.Subject = "This Weeks Reports".Body = strbody.Attachments.Add (attachmnt3).Display.SaveEnd .........

View 9 Replies View Related

Send An Email To Multiple Email Addresses

Oct 13, 2009

I am using the following code and it works great the only problem is that when I have more then one email address in the same cell it will not send the email. Even if I seperate it with a semicolon. It work fine if I have just one email address in the email field. How can I get it to send the same info to different email addresses.

View 4 Replies View Related

Pick Up Email ID And Send Automatic Email?

Jan 2, 2013

I have created a user Form where a user can enter all the details like (First name, last name, Email ID etc...). the entered data is submitted on Sheet1 and it is working for me.

Now which I want is the is it possible to pick up the last email id (Column D) and send an automatic email where email will be sent the last recipient?

Actually I want is that when a user will fill up the form I want to send him/her an email.

View 8 Replies View Related

Open Print Window To Change Printer And Select Number Of Copies?

Oct 5, 2011

The below code opens up the printpreview window. This does not allow to select the correct (or change) printer it will automatic use the default printer.

Is there a way for the code to open the print window so I can change the printer and select number of copies?

Code:

Sub Print_Button()
Dim ws As Worksheet, cell As Range
Set ws = Sheets("main")
Set cell = Range("g2000").End(xlUp)
Do Until cell.Value ""
Set cell = cell.Offset(-1, 0)
Loop
ws.PageSetup.PrintArea = ("A2:" & cell.Address)
ws.PrintPreview
End Sub

View 3 Replies View Related

Opens Up The Print Dialog Box For Print

Feb 26, 2007

I'm trying to find the code for a macro that I can link to a button so that when i click on it - it opens up the print dialog box.

View 5 Replies View Related

Send Data From Userform To Worksheet AND Send Userform Fields In Email?

Jul 12, 2014

I have used a database template from this site and changed it to suit my needs but I have a bit of a problem with some of the code. I know how to update the worksheet with the relevant userform text fields and in another project I did I have successfully sent userform text fields in the body of an email.

For this project I want to update the worksheet AND send an email at the same time. However, using the two pieces of code together is causing an error that I can't seem to solve (using my very limited vba knowledge!). The code I am working on is below and I have highlighted the line that is getting the error message. C

VB:
Private Sub cmdSubmit_Click() 'Submit new record
Dim ws As Worksheet, lRow As Long, Str As String [code]....

View 1 Replies View Related

Send Email Using Other Email Program

Feb 23, 2010

I have search on this forum regarding sending email on excel using outlook email application. I would like to ask if is it possible to use other email applications like AOL when sending email thru excel? I have attached a sample workbook.

View 9 Replies View Related

Code Before Print Add Inputbox To Change Date

Jun 1, 2007

I was wanting to input the date Before Printing. I want to write some code.

Private Sub Workbook Before_Print (cancel as booleen)
Inputbox = "Enter the date you would like in Mondays cell C4"
Application.Worksheet.Range("C4").Value = InputBox.Value
PrintDialogBox.Show
End Sub

I know I am making a very rough attempt at what I am trying to achieve, I just don't know coding that well.

View 9 Replies View Related

Open Print Dialog Box

Feb 23, 2010

I would like a macro to print 2 sheets in a workbook. I can write the macro that will select the sheets I want, but I don't want it to just go and print because depending on if the user wants it printed in color, b&w, etc.

they have to choose a different color. What code do I use to open the print dialog box?

View 2 Replies View Related

Print Dialog Seems To Resize A Text Box - ?

Feb 11, 2009

I have a (semi-inherited) macro that prints different pages of a workbook, based on whether and what their entries are. One of the pages contains a text box, where users will enter different lengths of text. When the macro runs, it prints right the first time but also "resizes" the text box so that future print attempts only print part of it.

I put "resize" in quotes because the text box does not appear to change shape. However, when I look at its properties it suddenly says it is much smaller than it was before (and still appears to be).

View 4 Replies View Related

Put Print Dialog Box Selection Into A Variable

Jan 16, 2012

Is there a way to have the print dialog box come up, make a selection, and put that selection into a variable?

Ex:

Code:

Application.Dialogs(xlDialogPrint).Show

brings up the print dialog. I need to be able to select the printer and have the selected printer put into a variable in my vba code.

I have tried Ex: w=Application.Dialogs(xlDialogPrint).Show, but all I get is w=true

The reason I need it is because I will be printing via vba code, but the printer may vary for different print jobs.

View 6 Replies View Related

Is There A VBA Way To Open Print Properties Dialog

Oct 19, 2007

I know I've seen where the print Dialog window can be opened.

Is there a way to open the Print PROPERTIES Dialog window?

View 9 Replies View Related

Print Dialog Seems To Resize A Text Box

Feb 18, 2009

I have a (semi-inherited) macro that prints different pages of a workbook, based on whether and what their entries are. One of the pages contains a text box, where users will enter different lengths of text. When the macro runs, it prints right the first time but also "resizes" the text box so that future print attempts only print part of it.

I put "resize" in quotes because the text box does not appear to change shape. However, when I look at its properties it suddenly says it is much smaller than it was before (and still appears to be).

FWIW, I'm using Excel 2003.

Here's the code in question (the problem is on the "narrative" page):

Sub CLCMprintall()
' prints all pages (with data) on the CLCM spreadsheet
Dim x, a, b, c, d, e, f, g, h, i As String
Dim y, z, q As Integer

ActiveSheet.Unprotect Password:="***"

'notes active cells on each sheet to return cursor there afterwards.
x = ActiveSheet.Name
Sheets("page 1").Select
a = ActiveCell.Address(True, True)
Sheets("narrative").Select
d = ActiveCell.Address(True, True)
'(repeat for other worksheets - also sets values for Loan2Amt, Q & Z,
'which govern whether other unrelated sheets need to be printed.)
'
'tests whether there is text in Overflow sheet and sets print area if there is
If q 0 Then
Call OverflowPrintArea.............

View 9 Replies View Related

Send The Email..?

Aug 12, 2009

I have a problem sending email thru excel, if i use this =HYPERLINK(CONCATENATE("mailto:",B2,"?subject=",C3,"&body=",D4),"Send e-mail") <-- this is working, but i want my body to also include not just d4, i tried to put name d4:g7 as body so the code would be: =HYPERLINK(CONCATENATE("mailto:",B2,"?subject=",C3,"&body=",body),"Send e-mail") <<-- the problem is this is not working ,

View 4 Replies View Related

Print The Worksheet On Different Paper Which Requires To Go Into The Properties And Change The Paper Source From Automatically Select To Manual Feed

Jun 30, 2006

When I hit the print button the worksheet prints on the paper in the bin. However, there are times when I need to print the worksheet on different paper which requires me to go into the properties and change the paper source from Automatically Select to Manual Feed. I have been trying to created a macro what will switch to Manual Feed, print the worksheet and then switch back to Automatically Select but have been unsucessfull.

View 3 Replies View Related

Send Email If Statement

Jul 30, 2014

I'm trying to program a send e-mail botton and everything is working well. However I would like to change the send to address from a specific cell to a if than statement.

I would like it to read along the lines of........ if "I5" = yes then copy cell from "M5"

[Code] .....

View 1 Replies View Related

Unable To Send Email Using CDO?

Mar 20, 2014

I'm trying to send a basic email using example [URL]..... but just cannot get it to work.

Here's my code and the error I get is "Run-time error '-2147220978 (8004020e)': The server rejected the sender address. The server response was: 530 5.7.1 Client was not authenticated."

I found my smtp server by looking in Outlook properties so I know that's right and for all instances of "person@email.com" I am putting my email address and the password is the normal password I use to log on.

View 1 Replies View Related

Send Spreadsheet Through Email

Jan 5, 2010

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:

View 3 Replies View Related

Send Email To Many Recipients

May 4, 2009

I feel like I almost have the Excel button I need (thanks to an exhaustive forum search), but I have 2 problems that I need help to correct. First, the code works fine if I have one email address in my Email column, but If I add a second or third email in my column, the code sends the mail, but bounces back because of an Invalid Internet address specified error. The response looks to me like it is taking the first address and appending to the second email recipient. An example would be <example@example.com,example>@example.com. Here is the code I am using:

View 3 Replies View Related

Save As PDF And Send Via Email

May 8, 2009

I have the code that saves it a a PDF, but a nicety would be to email it as well: -

inputfileSaveName = Application.GetSaveAsFilename(fileFilter:="PDF Files (*.pdf), *.pdf")

'export it - works just fine!
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=filesavename, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False

'OK up to here!

Application.Dialogs(xlDialogSendMail).Show arg1:="", _
arg2:=EmailTitle, arg3:=Receipt

It now sends the "workbook" as an attachment and not the PDF..

How do I remove the attachment and attach the PDF Ive just created?

View 3 Replies View Related

Send Email With TO And CC Recipients?

Apr 15, 2013

I have a macro currently using the activeworkbook.sendmail command to send out an email using LOTUS NOTES with the workbook as an attachment. Unfortunately, the code appears to only support putting recipients in the TO field. I need to be able to also add additional recipients under the CC field.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved