VBA Macro - Word

[MartinEbner]

Hi,

Issue
I have modified a macro (please see below) - it encompasses all of the functionality that I require with one exception. I require it to reference an email address that is written in the word document.

Macro
Sub eMailActiveDocument()
Dim OL As Object
Dim EmailItem As Object
Dim Doc As Document

Application.ScreenUpdating = True
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Doc = ActiveDocument
Doc.Save
With EmailItem
.Subject = "Subject"
.Body = "Dear Whoever,
.To = "User.Domain.Com"
.Importance = olImportanceNormal 'Or olImprotanceHigh Or olImprotanceLow
.Attachments.Add Doc.Fullname
.Display
End With

Application.ScreenUpdating = True

Set Doc = Nothing
Set OL = Nothing
Set EmailItem = Nothing

End Sub

-----------

Instead of it having a prescribed email in the macro, I need it to be flexible in the sense that from time to time the email address will change and as such it is impractical go into the macro every time - It needs to source it from the word document itself.

Any help would be greatly appreciated.

Regards,
Martin

 

[Macropod]

Do these email addresses appear in the document as clickable hyperlinks, or just as plain text? Is the email address in the header or footer; otherwise is it the first email address in the document?

 

[MartinEbner]

Hi Paul,

It is text not hyperlink. It will be the only email address within the document. The email address will not reside in the header or footer but in the body of the document.

Kind Regards,

 

[Macropod]

I see you have posted almost exactly the same question here:
http://www.mrexcel.com/forum/excel-questions/913375-vbulletin-macro-email-reference.html

The only difference I can see is that, in the other thread, you mention Excel - but even then the code you've posted is for Word.

Perhaps you could clarify your requirements...

 

[MartinEbner]

Hi,

The code is what I use in word and it serves it's purpose well. However, in order for it to be fully effective I need the macro to reference an email address in the document rather than having to physically put the email recipient into the document.

Regards,

 

[Macropod]

Cross-posted at: VBA Macro - Word
Please read our policy on Cross-Posting in rule #13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[MartinEbner]

Thanks for this guidance. . My mistake. Can you please support me further to my clarification?

 

[Macropod]

The code is what I use in word and it serves it's purpose well. However, in order for it to be fully effective I need the macro to reference an email address in the document rather than having to physically put the email recipient into the document.

So why in the other thread are you asking about Excel cells?

 

[MartinEbner]

Because the word document is a mail merge pulling data from the spreadsheet so my word macro can either reference from word or pull from Excel. . Do you understand.. Sorry that I've been unclear

 

[Macropod]

In that case, there is no need to automate Excel or Outlook, or refer to anything in the Word document. All you need do is retrieve the email address via the mailmerge code. For example:

Sub Merge_To_Emails()
Application.ScreenUpdating = False
Dim i As Long
With ActiveDocument
  For i = 1 To .MailMerge.DataSource.RecordCount
    With .MailMerge
      .Destination = wdSendToEmail
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
      End With
      .MailAddressFieldName = .DataSource.DataFields("Email_Address")
      .MailSubject = .DataSource.DataFields("Subject_Line")
      .MailAsAttachment = True
      .MailFormat = wdMailFormatHTML
      .Execute Pause:=False
    End With
  Next i
End With
Application.ScreenUpdating = True
End Sub