Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
pasting values
Ive tries this post in the programming section with no joy and im hoping somone can help me here The below works lovely however if there are links to other sheets that are not copied into the new book for emailing then i get formula errors. What would be good is if they were copied as values and not formula's. This would be need to be done keeping the format of the sheet. I believe ths could be done where i have put the *** Here *** marker 1. in column A - enter sheet or sheets name you want to send. 2. in column B - enter E-mail address. 3. in column C - the subject title appears at the top of the E-mail message. Column A:C enter information for the first mail and you may use columns D:F for the second one. you can send 85 different E-mails this way (85*3 = 255 columns). Sub Mail_sheets() Dim MyArr As Variant Dim last As Long Dim shname As Long Dim a As Integer Dim Arr() As String Dim N As Integer Dim strdate As String For a = 1 To 253 Step 3 If ThisWorkbook.Sheets("mail").Cells(1, a).Value = "" Then Exit Sub Application.ScreenUpdating = False last = ThisWorkbook.Sheets("mail").Cells(Rows.Count, a).End(xlUp).Row N = 0 For shname = 1 To last N = N + 1 ReDim Preserve Arr(1 To N) Arr(N) = ThisWorkbook.Sheets("mail").Cells(shname, a).Value Next shname ThisWorkbook.Worksheets(Arr).Copy strdate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss") *** Here *** ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _ & " " & strdate & ".xls" With ThisWorkbook.Sheets("mail") MyArr = .Range(.Cells(1, a + 1), .Cells(Rows.Count, a + 1).End(xlUp)) End With ActiveWorkbook.SendMail MyArr, ThisWorkbook.Sheets("mail").Cells(1, a + 2).Value ActiveWorkbook.ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName ActiveWorkbook.Close False Application.ScreenUpdating = True Next a End Sub Please help -- ceemo ------------------------------------------------------------------------ ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650 View this thread: http://www.excelforum.com/showthread...hreadid=487593 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Force values-only when pasting text to keep formatting | Excel Discussion (Misc queries) | |||
I Need a formula to evaluate a cell with + or - values | Excel Worksheet Functions | |||
Return Range of Numerical Values in Single Column based on Frequency Percentage | Excel Worksheet Functions | |||
#N/A Values : Returned by Formulas vs Entered Manually | Charts and Charting in Excel | |||
Excel clipboard should allow pasting of formulae not just values | Excel Discussion (Misc queries) |