CODEWITHSUNDEEP
vbaexcel
JeanLo
JeanLo

Reputation: 101

VBA convert Excel file to text when sending mail outlook

how can i convert the activeworkbook that i am working on , while sending a mail in outlook ? This code has like attachments .xlsx file , but i want it in text , how can i change it ? Thanks 

 'send mail code
                Set ol = New Outlook.Application
                Set olmail = ol.CreateItem(olMailItem)
                With olmail
                    .To = "[email protected]"
                    .Subject = objetText
                    .Body = "testing"
                    .Send

                .attachments.Add activeworkbook.fullname
                End With
            End sub

Upvotes: 0

Views: 959

Answers (3)

Axel Richter
Axel Richter

Reputation: 61870

With MS Office you can copy an Excel cell range in the clipboard and paste this into an Outlook mail body if that mail body is in rich text format and you are using Word editor. The cell range will be converted into a rich text table while this process.

This can also be accomplished by code:

Sub emailer()

 Set oOlApp = CreateObject("Outlook.Application")

 olMailItem = 0
 Set oOlMItem = oOlApp.CreateItem(olMailItem)

 'get Excel cell range which shall be in the mail
 Set oWB = ActiveWorkbook
 Set oWS = ActiveWorkbook.Worksheets(1)
 Set oRange = oWS.Range("A1:C10")

 oRange.Copy ' Range is now in Clipboard

 With oOlMItem

  .Display

  .To = "[email protected]"
  .Subject = "Subject"

  Set oOlInsp = .GetInspector
  Set oWdDoc = oOlInsp.WordEditor ' get Word Document from the MailBody

  olFormatRichText = 3
  .BodyFormat = olFormatRichText ' change to RichTextFormat

  Set oWdRng = oWdDoc.Paragraphs(oWdDoc.Paragraphs.Count).Range
  oWdRng.InsertBefore "This is before the Excel table."
  oWdRng.InsertParagraphAfter
  oWdRng.InsertParagraphAfter

  Set oWdRng = oWdDoc.Paragraphs(oWdDoc.Paragraphs.Count).Range
  oWdRng.Paste ' paste Excel range from Clipboard

  oWdRng.InsertParagraphAfter

  Set oWdRng = oWdDoc.Paragraphs(oWdDoc.Paragraphs.Count).Range
  oWdRng.InsertBefore "This is after the Excel table."


 End With

 Application.CutCopyMode = False

End Sub

Upvotes: 2

Rodger
Rodger

Reputation: 841

I am certain that Axel's answer is probably what you need but since you asked about converting to it to text and then putting it in, this will do that. The formatting will look terrible of course. Make sure that (in the VBE) you go into tools/references and place a check next to Microsoft Forms 2.0 Object Library.

Dim ws As Worksheet
Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
Set ws = ActiveWorkbook.ActiveSheet
    ws.UsedRange.Copy
DataObj.GetFromClipboard
strbody = DataObj.GetText(1)

With NewMail
  .SUBJECT = "Test Mail"
  .From = """Sender"" <[email protected]>"
  .To = "[email protected]"
  .CC = ""
  .BCC = ""
  .TextBody = strbody
End With

Upvotes: 0

Hello World
Hello World

Reputation: 308

You can only convert 1 worksheet to text, so I'm assuming this is what you are trying to accomplish. Copy the sheet to a new workbook (so you can continue working with the current workbook as xlsm), save that as text then use the save location for the string you're using for attachments.add

Dim wbkthis As Workbook
Dim wbkNew As Workbook

Set wbkthis = ActiveWorkbook 
'So we can come back here
Set wbkNew = Workbooks.Add 
'New book to copy sheet to

wbkthis.Sheets("relevantsheetname").Copy Before:=wbkNew.Sheets(1)

Application.DisplayAlerts = False
'Supress overwrite and close alerts, you may not want this

wbkNew.SaveAs "savelocation\Mybookname.txt", xlText 
'use this same string with your attachments.add code
wbkNew.Close

Application.DisplayAlerts = True
wbkthis.Activate ' Go back to original file

Upvotes: 0

Related Questions