PDA

View Full Version : [SOLVED] Sending email with links disabled in sheets



Hudson
11-20-2016, 03:42 AM
Hi Forum Experts ,

Can some one advice me with small tweak in the code that helps me sending email as values ..
to be more precise , i have code that sends email with selected sheets in a given workbook (" summary", " brakeup"). below is the code for your reference . everything is working fine until sending mails but the problem aroused at ... selected sheets links getting disabled and we are getting error (#VALUE!).. because summary sheet and brakeup sheets has formulas where its data source is interlinked with other sheets in the workbook..

any inputs are appreciated..

i have asked this question in outlook forum ..felt i should be asking this in this forum.


Option Explicit Sub ExportEmail()

Dim objfile As FileSystemObject
Dim xNewFolder
Dim xDir As String, xMonth As String, xFile As String, xPath As String
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Dim NameX As Name, xStp As Long
Dim xDate As Date, AWBookPath As String
Dim currentWB As Workbook, newWB As Workbook
Dim strEmailTo As String, strEmailCC As String, strEmailBCC As String, strDistroList As String

AWBookPath = ActiveWorkbook.Path & "\"

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.StatusBar = "Creating Email and Attachment for " & Format(Date, "dddd dd mmmm yyyy")

Set currentWB = ActiveWorkbook

xDate = Date

'******************************Grabbing New WorkBook and Formatting*************

Sheets(Array("Brake up", "Summary")).Copy

Set newWB = ActiveWorkbook

Range("A1").Select
Sheets("Brake up").Visible = True
'Sheets ("Email")


'******************************Creating Pathways*********************************

xDir = AWBookPath
xMonth = Format(xDate, "mm mmmm yy") & "\"

xFile = "Daily DA_DB_DZ analysis report " & Format(xDate, "dd-mm-yyyy") & ".xlsx"

xPath = xDir & xMonth & xFile

'******************************Saving File in Pathway*********************************

Set objfile = New FileSystemObject

If objfile.FolderExists(xDir & xMonth) Then
If objfile.FileExists(xPath) Then
objfile.DeleteFile (xPath)
newWB.SaveAs Filename:=xPath, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Application.ActiveWorkbook.Close
Else
newWB.SaveAs Filename:=xPath, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Application.ActiveWorkbook.Close
End If
Else
xNewFolder = xDir & xMonth
MkDir xNewFolder
newWB.SaveAs Filename:=xPath, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Application.ActiveWorkbook.Close
End If

'******************************Preparing Distribution List *********************************

currentWB.Activate
Sheets("Email").Visible = True
Sheets("Email").Select

strEmailTo = ""
strEmailCC = ""
strEmailBCC = ""

xStp = 1

Do Until xStp = 4

Cells(2, xStp).Select

Do Until ActiveCell = ""

strDistroList = ActiveCell.Value

If xStp = 1 Then strEmailTo = strEmailTo & strDistroList & "; "
If xStp = 2 Then strEmailCC = strEmailCC & strDistroList & "; "
If xStp = 3 Then strEmailBCC = strEmailBCC & strDistroList & "; "

ActiveCell.Offset(1, 0).Select

Loop

xStp = xStp + 1

Loop

Range("A1").Select

'******************************Preparing Email*********************************

Set olApp = New Outlook.Application
Dim olNs As Outlook.Namespace
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon
Set olMail = olApp.CreateItem(olMailItem)
olMail.To = strEmailTo
olMail.CC = strEmailCC
olMail.BCC = strEmailBCC


olMail.Subject = Mid(xFile, 1, Len(xFile) - 4)
olMail.Body = vbCrLf & "Hello Everyone," _
& vbCrLf & vbCrLf & "Please find attached the " & Mid(xFile, 1, Len(xFile) - 4) & "." _
& vbCrLf & vbCrLf & "Regards," _
& vbCrLf & "Genpact"


olMail.Attachments.Add xPath
olMail.Display

Application.StatusBar = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub

p45cal
11-20-2016, 09:02 AM
If you're only interested in sending files with plain values in it could be as simple as adding the following three lines directly below the code line:
Sheets("Brake up").Visible = True


For Each sht In newWB.Sheets
sht.UsedRange.Value = sht.UsedRange.Value
Next sht


If there are different types of links, or even named ranges local to sheets it might be a bit more involved.
There a article here ( http://excelribbon.tips.net/T007566_Replacing_Links_with_Values.html ) which could help.

If between the above two snippets of advice you still can't make it work properly, come back.

Hudson
11-21-2016, 12:51 PM
Thanks for your help mate . i did not try this . i will and let you know .

thanks again..

Hudson
11-22-2016, 04:21 AM
Hi Mate .

I tried above code and it is not happening . it is giving me " Runtime error". I have even added below dim

(Dim sheets as worksheets)


Can you advice please .

p45cal
11-22-2016, 06:16 AM
Is one of these sheets a chart sheet?
Which code line is highlighted when you get this error?

Hudson
11-22-2016, 06:29 AM
Not charts but it has formulas in both the sheets.

Its below code that is giving me error message.

" For Each sht In newWB.Sheets" error saying variable not define something like that .

p45cal
11-22-2016, 06:35 AM
That's different from Runtime error! Add the line
Dim sht
next to the other Dim statements.
Or temporarily remove/comment-out the line at the top of the module: Option Explicit

Hudson
11-22-2016, 06:57 AM
I am sorry . I am new bee to VBA .. can you elaborate please .


Or temporarily remove/comment-out the line at the top of the module: Option Explicit =I understand this part

p45cal
11-22-2016, 07:08 AM
1. At the top of your code in msg#1 you have:
Option Explicit Sub ExportEmail()This should be two lines:
Option Explicit
Sub ExportEmail()



Either:
2. Add the line Dim sht thus:
Option Explicit
Sub ExportEmail()
Dim sht
Dim objfile As FileSystemObject
Dim xNewFolder
Dim xDir As String, xMonth As String, xFile As String, xPath As String




OR:
3. Comment-out the Option Explicit line with an apostrophe thus:
'Option Explicit
Sub ExportEmail()but it wouldn't matter if you did both 2 and 3.

Hudson
11-22-2016, 07:48 AM
May bad . this time it is " Run time error 7"

Out of memory

p45cal
11-22-2016, 08:15 AM
" Run time error 7"

Out of memoryAt which line? (It may not highlight a line, but if it does…)
You should remove the line you added that you mentioned in post #4 (Dim sheets as worksheets); it's not necessary.
Are these worksheets really big?
Are you running a bunch of other applications - a browser maybe, or another Office application (even Outlook doesn't need to be running since the code starts its own instance of it). Close 'em.
How much RAM on your machine? Add more (!)

Oh, and one more thing, if you've been running this code several times it's possible that you'll have multiple invisible instances of Outlook running since there's no closing of it at the end of your code. Look in Task Manager to see if there are multiple instances running. This might be gobbling up memory.

Hudson
11-22-2016, 12:53 PM
Ok mate . i will check . and thanks for your help .