エクセルシートをVBAでPDFファイルで保存する

質問

いつもお世話になっております。

先日ここで、(1)エクセルシートから自動でPDFファイルを作成し、(2)所定のフォルダに自動で保存し、(3)作成されたPDFファイルを決まった宛先に自動送信する、というVBAのコードを教わって非常に満足して使い始めたのですが、使ってみたらどんどん欲が出てきて３度目の質問です。

上記のコードでは、PDFファイルが「送信.pdf」で上書きされてしまう事は分かっていたのですが、メールの送信日時で区別できるので送信BOXから手動で専用BOXに移動すれば良いと思ったのですが、やはりPDFファイルを所定のフォルダーに自動で保存できれば一気に完了するので何とかならないかと。
一応NETで「日付をファイル名にする」といういくつかのコードを調べて種々試行錯誤してみたのですが、当方の実力では下記のVBAコードに組み込むことはできないとの結論に至りました。（情けない限り）

追加したいことは自動作成されるpdfファイルを上書きされずに所定のフォルダに自動保存する、です。
ファイル名としては特定のセルの値（これがbest)、もしくは日付（yyyy/dd/mm）でも十分です。
とにかく自動作成されるPDFファイルが所定のフォルダに上書きされずに保存されればOKなのですが。

本当に何度も追加質問をすることになってしまって申し訳ないのですが宜しくお願いいたします。

Sub Test()
Dim FilePath As String, strSub As String
Dim OutlookApp As Object
Dim OutlookMail As Object

'On Error Resume Next
FilePath = "\\ABC\123\あいう\アイウ\サンプル名\結果報告書\送信.pdf"
'Sheet3の結果報告書の特定のセルに品名
strSub = Worksheets("結果報告書").Range("E14").Value & "XXXX" & "LOT " & Range("E19")
Worksheets("結果報告書").ExportAsFixedFormat Type:=xlTypePDF, Filename:=FilePath
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
With OutlookMail
.To = "*****@xxxxx.co.jp"
.CC = ""
.BCC = ""
.Subject = strSub
.Body = "表題の件添付の通りです。"
.Attachments.Add FilePath
.Send
End With
　 MsgBox "送信完了"
End Sub

回答No.2

watabe007 · ベストアンサー

ファイル名にstrSubを加えるのも解りやすいかも
strSub = Worksheets("結果報告書").Range("E14").Value & "XXXX" & "LOT " & Range("E19")
FilePath = "\\ABC\123\あいう\アイウ\サンプル名\結果報告書\送信" & strSub & Format(Now(), "yymmdd") & ".pdf"

お礼

今帰宅しました。
今回は本当にありがとうございました。
また機会にはよろしくお願いいたします。

Thank you very very much again！！！

補足

No1,No2とどちらもうまく動くのですが、違いが分かりません。
とりあえず改良版と思われるNo2で使います。

回答No.4

watabe007

私もakira0723さんのお蔭でOutlookを学ぶ機会を与えていただき感謝しております。
これは、こんな事も出来る って、お知らせです。
.Sendはコメントアウトしており発信はしませんのでお試しください。
Sub 特別篇()
　　Dim FilePath As String, strSub As String
　　Dim OutlookApp As Object
　　Dim OutlookMail As Object

　　strSub = Worksheets("結果報告書").Range("E14").Value & "XXXX" & "LOT " & Range("E19")
　　Set OutlookApp = CreateObject("Outlook.Application")
　　Set OutlookMail = OutlookApp.CreateItem(0)
　　With OutlookMail
　　　　.To = "*****@xxxxx.co.jp"
　　　　.CC = ""
　　　　.BCC = ""
　　　　.Subject = strSub
　　　　.BodyFormat = 3
　　　　.Display
　　　　.Body = "表題の件、下記の通りです。"
　　　　With .GetInspector.WordEditor
　　　　　　With Worksheets("結果報告書")
　　　　　　　　.Range(.PageSetup.PrintArea).Copy
　　　　　　End With
　　　　　　.Characters.Last.Paste
　　　　　　.Characters.Last.InsertBefore vbCr & "お手数おかけしますが、何卒よろしくお願いいたします。 "
　　　　End With
'　　　　.Send
　　End With
　　MsgBox "送信完了"
End Sub

お礼

何度も恐れ入ります。（だんだんお礼とお詫びのフレーズが無くなってきました）

朝一でこれを試して、なるほど！の出来栄え確認しました。
指定した印刷範囲だけメール本文に抽出されるのは便利で他で使えそうです。適当な個所にコメントも組み込魔れているので汎用性があるので助かります。

今回はpdfファイルが必要なので最初のVBAの完成を目指してもう一息頑張ります。

回答No.3

watabe007

Format(Now(), "yymmdd") だと同日に送信すると上書きされますね
時間、分を加えて
Format(Now(), "yymmddhhnn")
で、如何ですか

補足

watabe007さん
本当に申し訳ありません！
これまでの実績からおそらく1発でうまく動いてこれで完結できると思います。

同日に送信すると上書きされることは想定内ですが、1日に２LOT生産されることは無いので問題なし、と思ったのですが、極まれに再発行もあり得るので時間まで入れておけば更に完璧です。
少しでも行数の少ないコードをと思い、結局3度もお手数をおかけすることになってしまい本当に申し訳ありませんでした。

お礼とBSは明日以降の動作確認後にさせていただきます。
当方のことなので何が起こっても不思議はないので、ここまで来たら最後の最後までお世話になって完成形にさせたいので後しばらくよろしくお願いいたします。

回答No.1

watabe007

akira0723 さん、こんにちは、頑張っていますね
以下、お試しください。
>FilePath = "\\ABC\123\あいう\アイウ\サンプル名\結果報告書\送信.pdf"
　　　　↓
FilePath = "\\ABC\123\あいう\アイウ\サンプル名\結果報告書\送信" & Format(Now(), "yymmdd") & ".pdf"

お礼

とにかく負んぶに抱っこ状態での試行錯誤だったので落ち着けば分かるハズのことも分からなくなってしまったので余計にお手数をおかけしてしまいました。

今度こそ完結版となりました。

このエクセルシートの使い方（入力⇒リンク⇒送信までの作成手順）をマニュアルにして残しておきます。
マニュアルの最後に　「by watabe007」を記載しておきます。

複数のアドレスへの送信、複数のｃｃへの送信はすでに確認済みなので、残る作業は、いくつかのフォルダに自動で出来たpdfファイルの削除と、同じ数だけの受信メールの削除ですがこれは手動で実施します。（笑）

長い間根気強くご指導いただき感謝！、感激！！です。

補足

毎度お世話様です。

最初にNo4を確認してから、下の回答から順に確認していこうとして本当にお恥ずかしい話ファイルパスや保存のファイル名を少しいじるだけでどこかおかしくなってしまいました。実際のファイルパスは恐ろしく長く顧客名やら品名が入っているのでどこか抜けるようです。（ほんの少し、本当に少しだけ自作部分もあるので）

その時のために昨夜時点の完成版のコードをワードに貼り付けておいたので何度か元に戻しながらやっとファイル名が「"日付"」のみで目的の新規作成の専用フォルダに保存されるようになりました。

混乱した原因は
FilePath = "\\ABC\123\あいう\アイウ\サンプル名\結果報告書\送信" & Format(Now(), "yymmdd") & ".pdf"

の送信の前の￥まで削除していたのが原因でした。

普通にパスを指定するときには気付くと思うのですが、パスの入力ミスも重なるのでこれに気付くのに何度も試行錯誤してしまいました。

ついでに当然時間も入るものだと思い込んでNo3を試したらまたまたトラブル発生。
この件の再度の質問はあり得ないので一旦は時間は基本不要なのであきらめるかと思いましたが、よく見たら
＞Format(Now(), "yymmddhhnn")
となっていました。(笑い）