Try replacing "email address goes here" (including the quotes) with:
Hope that helps,Code:wb.Worksheets("Sheet1").Tange("T20")
I am trying to use VBA to do the following:
1) copy a specified range (for example A4:H72)
2) open a new worksheet and paste as values the range copied from step 1
3) email the newly created spreadsheet to an email address that is located in a specified cell in the original worksheet (for example T20)
I have a working code that sends the new worksheet to a hard-coded recipient as seen below but I need the email address to change without having to change the code. Please help!
Note, I cant take credit for the code below. I borrowed it so kudos to the original owner!
Code:Sub Mail_Range() 'HARDCODED TO MAIL TO ONE PERSON Dim Source As Range Dim Dest As Workbook Dim wb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim I As Long Set Source = Nothing On Error Resume Next Set Source = Range("A4:h72").SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Source Is Nothing Then MsgBox "The source is not a range or the sheet is protected, " & _ "please correct and try again.", vbOKOnly Exit Sub End If With Application .ScreenUpdating = False .EnableEvents = False End With Set wb = ActiveWorkbook Set Dest = Workbooks.Add(xlWBATWorksheet) Source.Copy With Dest.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With TempFilePath = Environ$("temp") & "\" TempFileName = "Range of " & wb.Name & " " _ & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then FileExtStr = ".xls": FileFormatNum = -4143 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If With Dest .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next For I = 1 To 3 .SendMail "email address goes here", _ "This is the Subject line" If Err.Number = 0 Then Exit For Next I On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file you have send ' Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub
Try replacing "email address goes here" (including the quotes) with:
Hope that helps,Code:wb.Worksheets("Sheet1").Tange("T20")
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
ok, i tried it (see below, just in case i typed it incorrectly) but it is still not working. It performs perfectly right up to the point of emailing but then it dies. It creates the spreadsheet, pastes as values, but then stops with no error messages..
.SendMail wb.Worksheets("Sheet1").Tange("T20"), _
"This is the Subject line"
If Err.Number = 0 Then Exit For
Ah, sorry. Made a spelling mistake in the browser. Should be:
My apologies!Code:wb.Worksheets("Sheet1").Range("T20")
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
That worked perfectly! I was even able to take it a step further and have the Email Subject change based on another cell value! Thank you SOOO much!
My workday just got a little more automated.
Tim
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
Bookmarks