Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / August 2007

Tip: Looking for answers? Try searching our database.

excel/word macro conversion

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
andrewbt - 31 Aug 2007 14:44 GMT
Hello all.  I am currently using the code below for setting up an e-mail
function in excel.  If anyone here knows of any way to transfer this macro
for it to work on a word document rather than my excel sheet then any help
would be greatly appreciated.  Thanks in advance.

Sub correctionmail()
   Dim FileExtStr As String
   Dim FileFormatNum As Long
   Dim Sourcewb As Workbook
   Dim Destwb As Workbook
   Dim TempFilePath As String
   Dim TempFileName As String
   Dim OutApp As Object
   Dim OutMail As Object
   With Application
       .ScreenUpdating = False
       .EnableEvents = False
   End With
   Set Sourcewb = ActiveWorkbook
   ActiveSheet.Copy
   Set Destwb = ActiveWorkbook
   With Destwb
       If Val(Application.Version) < 12 Then
           FileExtStr = ".xls": FileFormatNum = -4143
       Else
           If Sourcewb.Name = .Name Then
               With Application
                   .ScreenUpdating = True
                   .EnableEvents = True
               End With
               MsgBox "Your answer is NO in the security dialog"
               Exit Sub
           Else
               Select Case Sourcewb.FileFormat
               Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
               Case 52:
                   If .HasVBProject Then
                       FileExtStr = ".xlsm": FileFormatNum = 52
                   Else
                       FileExtStr = ".xlsx": FileFormatNum = 51
                   End If
               Case 56: FileExtStr = ".xls": FileFormatNum = 56
               Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
               End Select
           End If
       End If
   End With

   TempFilePath = Environ$("temp") & "\"
   TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy
h-mm-ss")

   Set OutApp = CreateObject("Outlook.Application")
   OutApp.Session.Logon
   Set OutMail = OutApp.CreateItem(0)

   With Destwb
       .SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
       On Error Resume Next
       With OutMail
           .To = ""
           .CC = ""
           .BCC = ""
           .Subject = ""
           .Body = ""
           .Attachments.Add Destwb.FullName
           .Display send it.
       End With
       On Error GoTo 0
       .Close SaveChanges:=False
   End With
   Kill TempFilePath & TempFileName & FileExtStr
   Set OutMail = Nothing
   Set OutApp = Nothing
   With Application
       .ScreenUpdating = True
       .EnableEvents = True
   End With
End Sub
Shauna Kelly - 31 Aug 2007 15:22 GMT
Hi

The following should be a good start:

How to send an email from Word using VBA
http://www.word.mvps.org/FAQs/InterDev/SendMail.htm

Hope this helps.

Shauna Kelly.  Microsoft MVP.
http://www.shaunakelly.com/word

> Hello all.  I am currently using the code below for setting up an e-mail
> function in excel.  If anyone here knows of any way to transfer this macro
[quoted text clipped - 77 lines]
>    End With
> End Sub

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.