Macro to Email several sheets, but not to email those where A2 is blank

[howard]

I have macro to email several sheets, but do not want to attach those sheets where A2 is blank


It would be appreciated if someone could amend my code

Sub Email_salesheets()[/TD] [/TR] [TR] [TD][/TD] [/TR] [TR] [TD]Dim Ztext As String[/TD] [/TR] [TR] [TD]Dim Zsubject As String[/TD] [/TR] [TR] [TD][/TD] [/TR] [TR] [TD][/TD] [/TR] [TR] [TD][/TD] [/TR] [TR] [TD]Ztext = [bodytext] 'read in text from named cell[/TD] [/TR] [TR] [TD]Zsubject = [SubjectText][/TD] [/TR] [TR] [TD][/TD] [/TR] [TR] [TD]Sheets(Array("sales1", "sales2", "sales3")).Copy[/TD] [/TR] [TR] [TD][/TD] [/TR] [TR] [TD]With Range("A1:O150")[/TD] [/TR] [TR] [TD].Value = .Value[/TD] [/TR] [TR] [TD]End With

With ActiveWorkbook

'.Windows(1).Visible = False

Application.DisplayAlerts = False

.SaveAs Environ("TMP") & "\" & ThisWorkbook.Sheets("sales1").Name & ".xlsx"

Application.DisplayAlerts = True

.Close (True)

End With

With CreateObject("Outlook.Application").CreateItem(0)

.To = Join(Application.Transpose(Sheets("Email sales").Range("AA1:AA3").Value), ";")

.Subject = Zsubject

.Body = Ztext

.Attachments.Add Environ("TMP") & "\" & ThisWorkbook.Sheets("sales1").Name & ".xlsx"

.Display

'.send

End With

End Sub

 

[6StringJazzer]

Sorry but the way you have pasted in your code makes it unreadable. My recommendation is to edit your post, or add a new post. Go to your VBA and Copy it, then go to your post and Paste it. Then highlight all the code in your post and click the VBA button in the edit controls.

 

[howard]

Hi Jeff

My apologies. There is no edit button, So am going to create a new post

 

[howard]

Hi Jeff


See code below

 Sub Email_salesheets()



Dim Ztext As String

Dim Zsubject As String







Ztext = [bodytext] 'read in text from named cell

Zsubject = [SubjectText]



Sheets(Array("sales1", "sales2", "sales3")).Copy



With Range("A1:O150")

.Value = .Value

End With



With ActiveWorkbook

'.Windows(1).Visible = False

Application.DisplayAlerts = False

.SaveAs Environ("TMP") & "\" & ThisWorkbook.Sheets("sales1").Name & ".xlsx"



Application.DisplayAlerts = True

.Close (True)

End With



With CreateObject("Outlook.Application").CreateItem(0)

.To = Join(Application.Transpose(Sheets("Email sales").Range("AA1:AA3").Value), ";")

.Subject = Zsubject

.Body = Ztext



.Attachments.Add Environ("TMP") & "\" & ThisWorkbook.Sheets("sales1").Name & ".xlsx"

.Display

'.send

End With



End Sub

 

[6StringJazzer]

I only see you attaching one file here. I also don't understand what the intent is of your code. For example,

.SaveAs Environ("TMP") & "\" & ThisWorkbook.Sheets("sales1").Name & ".xlsx"

This code saves the activeworkbook. It seems to be saving it in a temp folder just for the purpose of being able to attach it later. Also sheets("sales1").Name is just "sales1". It's kind of a pointless operation. This code will save a file called sales1.xlsx.

.Attachments.Add Environ("TMP") & "\" & ThisWorkbook.Sheets("sales1").Name & ".xlsx"

As above, this code is attaching a file from your TMP folder called sales1.xlsx.

You are just attaching a copy of the active workbook. Why not just attach the active workbook itself and skip all of this saving?

.Attachments.Add ActiveWorkbook.FullName

None of this answer your question but I'm still unclear as to the question. Do you want to attach a version of the active workbook that removes any sheets where A2 is blank?

 

[howard]

Thanks for the advice