View Single Post
  #7  
Old 09-03-2010, 07:32 AM
deity deity is offline
PA Super User
 
Join Date: May 2007
Posts: 58
Thanks: 3
Thanked 3 Times in 2 Posts
Question Vba

May be for Outlook x64 simple to create VBA code, like this for WinZIp?
Code:
Sub ActiveWorkbook_Zip_Mail()
'This sub will send a newly created workbook (copy of the Activeworkbook).
'It zip and save the workbook before mailing it with a date/time stamp.
'After the zip file is sent the zip file and the workbook will be deleted
from your hard disk.
Dim PathWinZip As String, FileNameZip As String, FileNameXls As String
Dim ShellStr As String, strdate As String
Dim Runwzzip As Long
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem

PathWinZip = "C:\program files\winzip\"
'This will check if this is the path where WinZip is installed.
If Dir(PathWinZip & "winzip32.exe") = "" Then
MsgBox "Please find your copy of winzip32.exe and try again"
Exit Sub
End If

esaName = ActiveSheet.Range("f6").Value
seqNumber = ActiveSheet.Range("b6").Value
FileNameZip = "C:\rds\zipped\" & seqNumber & " " & esaName & ".zip "
FileNameXls = "C:\rds\zipped\" & seqNumber & " " & esaName & ".xls"
ActiveWorkbook.SaveCopyAs FileName:=FileNameXls

ShellStr = PathWinZip & "Winzip32 -min -a " _
& " " & Chr(34) & FileNameZip & Chr(34) _
& " " & Chr(34) & FileNameXls & Chr(34)
Runwzzip = Shell(ShellStr, vbHide)


nSubject = ActiveSheet.Range("b6").Value
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

With OutMail
.To = "Email here"
.CC = ""
.BCC = ""
.Subject = nSubject
.Body = " "
.Attachments.Add FileNameZip
.Send


End With

Set OutMail = Nothing
Set OutApp = Nothing
Kill FileNameXls

End Sub
Reply With Quote